
package com.scalar.chiptrack.operations.packingslip.dao;

import java.util.Arrays;
import java.util.List;
import java.util.HashSet;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Date;
import java.util.Calendar;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.struts.util.LabelValueBean;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.utils.SqlQueryMappings;

import com.scalar.chiptrack.sales.SalesOrder;
import com.scalar.chiptrack.sales.Customer;
import com.scalar.chiptrack.sales.dao.CustomerDAO;
import com.scalar.chiptrack.sales.dao.SalesOrderDAO;
import com.scalar.chiptrack.sales.LineItem;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.Mailer;

import com.scalar.chiptrack.operations.packingslip.CustomerShippingLotNumberData;
import com.scalar.chiptrack.operations.packingslip.CustomerShippingLineItem;
import com.scalar.chiptrack.operations.packingslip.forms.PackingSlipForm;
import com.scalar.chiptrack.operations.packingslip.forms.CommercialInvoiceForm;
import com.scalar.chiptrack.operations.packingslip.FinalInventoryData;
import com.scalar.chiptrack.operations.packingslip.PackingSlip;
import com.scalar.chiptrack.users.dao.UserDAO;

// BR, Mar 13, 2004
import com.scalar.chiptrack.operations.packingslip.forms.ShippingAdviseForm;

public final class PackingSlipDAO implements Constants 
{
    public static void confirmPackingSlip( int customerShippingId, int noOfBoxes, String[] tracking, String userId, String shippedVia, String weight, String contactPerson, String invoiceNo, String shipDate, String grossWeight ) throws ScalarException
    {

        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;
        try
        {
            String tempWeight       = "";
            String grossWeightStr   = "";
            try
            {
                if ( ( weight != null ) && ( weight.trim().length() != 0 ) )
                {
                    tempWeight = "" + new Double( weight ).doubleValue();
                }
                else
                {
                    tempWeight = null;
                }
            }
            catch(Exception e){ tempWeight = null; }

            try
            {
                if ( ( grossWeight != null ) && ( grossWeight.trim().length() != 0 ) )
                {
                    grossWeightStr = "" + new Double( grossWeight ).doubleValue();
                }
                else
                {
                    grossWeightStr = null;
                }
            }
            catch(Exception e){ grossWeightStr = null; }

            conn = DataSource.getConnection();

            // Step 1:Update packingslip status
            cstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.updatePackingSlipStatus"));
            cstmt.setInt    ( 1, customerShippingId );
            cstmt.setString ( 2, CUSTOMER_SHIPPING_STATUS_SHIPPED );
            cstmt.setInt    ( 3, noOfBoxes ) ;
            cstmt.setString ( 4, StringUtils.concatStrings( tracking, ";" ) );
            cstmt.setString ( 5, shippedVia );
            cstmt.setString ( 6, tempWeight);
            cstmt.setString ( 7, contactPerson );
            cstmt.setString ( 8, invoiceNo );
            cstmt.setString ( 9, shipDate );

            cstmt.executeUpdate();

            cstmt.close();
            cstmt = null;

            // Step 2:Insert a row in Customer_Shipped_order_line_item table
            cstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.insertShippedOrderLineItem"));
            cstmt.setInt (1, customerShippingId);
            cstmt.executeUpdate();
            cstmt.close();
            cstmt = null;

            // Step 3:Update status in Customer_Open_Order_Line_Item table
            cstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.updateOpenOrderLineItemStatus"));
            cstmt.setInt (1, customerShippingId);
            cstmt.setString ( 2, CUSTOMER_SHIPPING_STATUS_SHIPPED );
            cstmt.setString (3, userId);
            cstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
              System.out.println( "DAO :error : " + sqle);
            throw new ScalarException ("Could not cancel packing", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }


    // changed by hareesh on 29th July 2003
    // -- STRAT --
    public static void cancelPackingSlip (int customerShippingId) throws ScalarException {
            cancelPackingSlip (null, customerShippingId);
    }

    public static void cancelPackingSlip (DBTransaction dbTransaction, int customerShippingId) throws ScalarException {

        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;


        /*Bhaskar added this var on 7 Jan 2004. This checks whether the validation is associated
        with a drop shipment related transaction or packing slip related transaction.
        */
        boolean isDropShipment = selectPackingSlipType( customerShippingId );

        try {
            //conn = DataSource.getConnection();
            if(dbTransaction != null){
                conn = dbTransaction.getConnection();
            } else{
                conn = DataSource.getConnection();
            }

            if (isDropShipment)
            {
                cstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.cancelPackingSlip"));
                cstmt.setString ( 3, "admin" );                        
            }
            else
            {
                cstmt = conn.prepareCall (SqlQueryMappings.get ("operations.dropshipment.cancelPackingSlip"));
            }
            cstmt.setInt (1, customerShippingId);
            cstmt.setString (2, CUSTOMER_SHIPPING_STATUS_CANCELLED);
            cstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
            throw new ScalarException ("Could not cancel packing", sqle);
        }
        finally {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try
                {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }


    // Added by Balaram, June 23, 2003
    public static String[] loadVendorEmailsForPackingSlip() throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String emails = null;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.loadVendorEmailsForPackingSlip" ) );
            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                emails = rset.getString( "packing_slip_emails" );
            }
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not retrieve PackingSlip email list from database", e );
        }
        finally
        {
            if( rset != null )
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        String[] emailArray = StringUtils.toStringArray( emails, Mailer.EMAIL_SEPARATOR );

        if( ( emailArray != null ) && ( emailArray.length > 0 ) )
        {
            Arrays.sort( emailArray );
        }

        return emailArray;
    }


    public static ArrayList loadPackingSlipsBySalesOrderId (int salesOrderId, String status) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList packingSlipList = new ArrayList();

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.loadPackingSlipsBySalesOrderId"));
            pstmt.setInt (1, salesOrderId);
            pstmt.setString (2, status);
            rset = pstmt.executeQuery();

            PackingSlipForm packingSlipForm = null;
            while (rset.next()) {
                packingSlipForm = new PackingSlipForm();
                packingSlipForm.setCustomerShippingId (rset.getInt ("CustomerShipping_ID"));
                packingSlipForm.setLabel (rset.getString ("Label"));

                packingSlipList.add (packingSlipForm);
            }
        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve PackingSlip list from database", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return packingSlipList;
    }


    public static ArrayList loadAllPackingSlipsBySalesOrderId (int salesOrderId) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList packingSlipList = new ArrayList();

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.loadAllPackingSlipsBySalesOrderId"));
            pstmt.setInt (1, salesOrderId);

            rset = pstmt.executeQuery();

            PackingSlipForm packingSlipForm = null;
            while (rset.next()) {
                packingSlipForm = new PackingSlipForm();
                packingSlipForm.setCustomerShippingId (rset.getInt ("CustomerShipping_ID"));
                packingSlipForm.setLabel (rset.getString ("Label"));

                packingSlipList.add (packingSlipForm);
            }
        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve PackingSlip list from database for salesOrder ID: " + salesOrderId, e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return packingSlipList;
    }



    // Return number of packingslips generated for the give salesOrderId
    // Balaram Putta on May 12, 2003
    public static int getTotalNumberOfPackingSlipsGenerated (String salesOrderId) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int total = 0;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.getTotalNumberOfPackingSlipsGenerated"));
            pstmt.setString (1, salesOrderId);
            rset = pstmt.executeQuery();

            if (rset.next()) {
                total = rset.getInt ("total");
            }
        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve the total number of PackingSlip generated for the sales order ID: " + salesOrderId, e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return total;
    }


    public static List loadAllPackingSlipSalesOrdersIds() throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        HashSet salesOrderIdSet = new HashSet();

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.loadAllPackingSlipSalesOrdersIds"));
            rset = pstmt.executeQuery();

            while (rset.next()) {
                salesOrderIdSet.add (rset.getInt ("SalesOrder_ID")+"");
            }
        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve PackingSlip list from database", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return Arrays.asList(salesOrderIdSet.toArray());
    }


    public static HashMap loadShippableLineItemMapBySalesOrderId (int salesOrderId) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        Connection conn1 = null;
        CallableStatement pstmt1 = null;

        HashMap lineItemMap = new HashMap();

        boolean foundRecords = false;

        try {
            conn = DataSource.getConnection();

            // Step 1
            // Get all Line Items for this sales order
            pstmt = conn.prepareCall (SqlQueryMappings.get ("sales.customer.loadApprovedLineItemIdsBySalesOrderId"));
            pstmt.setInt (1, salesOrderId);
            rset = pstmt.executeQuery();

            int lineItemId = -1;

            while (rset.next()) {
                lineItemId = rset.getInt ("LineItem_ID");
                lineItemMap.put (lineItemId +"", null);
            }

            rset.close();
            pstmt.close();
            pstmt = null;

            // Step 2
            // See if there are any Line Items that are still pending to be
            pstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.loadLineItemMapBySalesOrderId"));
            pstmt.setInt (1, salesOrderId);
            rset = pstmt.executeQuery();

            CustomerShippingLineItem lineItem = null;

            int lineItemId1 = -1;
            int qtyToBeShipped = 0;

            conn1 = DataSource.getConnection();
            pstmt1 = conn1.prepareCall (SqlQueryMappings.get ("operations.packingslip.getPendingQuantityForLineItem"));

            while (rset.next()) {

                lineItemId1 = rset.getInt ("LineItem_ID");
                // Get pending quantity for this line item
                // Returns the quantity yet to be shipped
                pstmt1.setInt (1, lineItemId1);
                pstmt1.registerOutParameter (2, java.sql.Types.INTEGER);

                pstmt1.executeQuery();
                qtyToBeShipped = pstmt1.getInt (2);
                pstmt1.clearParameters();

                if (qtyToBeShipped > 0) {   // Add the lineItem only if we still have some quantity to be shipped
                    lineItem = new CustomerShippingLineItem (lineItemId1, 0/*quantity_shipped*/, qtyToBeShipped);
                    lineItemMap.put (lineItemId1 +"", lineItem);
                }
                else {                      // Or else remove if the map already has an entry
                    lineItemMap.remove (lineItemId1+"");
                }

                foundRecords = true;
            }

        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve CustomerShipping LineItems for salesOrderId - " + salesOrderId, e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (pstmt1 != null) {
                try { pstmt1.close(); } catch (SQLException ignore) {}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
            if (conn1 != null) {
                try { conn1.close(); } catch (SQLException ignore) {}
            }
        }

        if ((foundRecords == true) && (lineItemMap.isEmpty() == true)) {
            throw new ScalarException ("No Line Items are pending. All are shipped");
        }

        return lineItemMap;
    }



    public static HashMap loadCustomerShippingLotNumberData (int packingSlipId, int lineItemId) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        HashMap csLotNumberDataMap = new HashMap();

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.loadCustomerShippingLotNumberData"));
            pstmt.setInt (1, packingSlipId);
            pstmt.setInt (2, lineItemId);
            rset = pstmt.executeQuery();

            int lotId = -1;
            int qtyPull = -1;
            int cslitId = -1;
            String lotNumber = null;

            while (rset.next()) {
                cslitId = rset.getInt ("CSLIT_ID");
                lotId = rset.getInt ("Lot_ID");
                qtyPull = rset.getInt ("CSLNT_Qty_Pull");
                lotNumber = rset.getString ("Lot_Number");
                csLotNumberDataMap.put (lotId +"", new CustomerShippingLotNumberData (cslitId, lotId, lotNumber, qtyPull));
            }
        }
        catch (Exception e) {
            e.printStackTrace();
            System.out.println ("Could not load CustomerShipping Lot Number information for packingslip Id- " + packingSlipId + ", and line itemID - " + lineItemId);
            throw new ScalarException ("Could not load CustomerShipping Lot Number information for packingslip Id- " + packingSlipId + ", and line itemID - " + lineItemId, e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return csLotNumberDataMap;
    }


    public static HashMap loadCustomerShippingLineItemQuantityById (int customerShippingId) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        HashMap csLineItemQtyMap = new HashMap();

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.loadCustomerShippingLineItemQuantityById"));
            pstmt.setInt (1, customerShippingId);
            rset = pstmt.executeQuery();

            while (rset.next()) {
                csLineItemQtyMap.put (rset.getInt ("LineItem_ID")+"", rset.getInt ("CSLIT_Qty_Shipped")+"");
            }
        }
        catch (Exception e) {
            throw new ScalarException ("Could not load CustomerShipping Line Item details for Customer Shipping ID:  " + customerShippingId, e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return csLineItemQtyMap;
    }


    public static HashMap loadCustomerShippingLotNumberDataMap (int csLineItemId) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        HashMap csLotNumberDataMap = new HashMap();

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.loadCustomerShippingLotNumberDataById"));
            pstmt.setInt (1, csLineItemId);
            rset = pstmt.executeQuery();

            int lotId = -1;
            int qtyPull = -1;

            while (rset.next()) {
                lotId = rset.getInt ("Lot_ID");
                qtyPull = rset.getInt ("CSLNT_Qty_Pull");
                csLotNumberDataMap.put (lotId +"", new CustomerShippingLotNumberData (csLineItemId, lotId, qtyPull));
            }
        }
        catch (Exception e) {
            throw new ScalarException ("Could not load CustomerShipping Lot Number information for csLineItemId - " + csLineItemId, e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return csLotNumberDataMap;
    }


    public static PackingSlipForm loadPackingSlipById (int id) throws ScalarException
    {

        Connection conn                 = null;
        Connection conn1                = null;
        PreparedStatement pstmt         = null;
        CallableStatement pstmt1        = null;
        ResultSet rset                  = null;
        PackingSlipForm packingSlipForm = null;
        String weigt                    = "";
        String grossWeight              = "";

        try
        {
            conn = DataSource.getConnection();

            // Step 1:Load data from Customer_Shipping Table
            pstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.loadPackingSlipById"));
            pstmt.setInt (1, id);
            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                String label = rset.getString ("Label");
                int salesOrderId = rset.getInt ("SalesOrder_ID");
                SalesOrder salesOrder = SalesOrderDAO.loadSalesOrder (salesOrderId+"");
                Customer customer = CustomerDAO.loadCustomerById (salesOrder.getCustomerId());

                packingSlipForm = new PackingSlipForm ( label,
                                                          id,
                                                          salesOrder.getSalesOrderId(),
                                                          salesOrder.getSalesOrderNo(),
                                                          salesOrder.getCustomerName(),
                                                          salesOrder.getCustomerPO(),
                                                          rset.getString ("CST_Shipping_Contact_Person"),
                                                          customer.getRepOffice(),
                                                          salesOrder.getDeliveryTerms(),
                                                          rset.getString ("CST_FOB"),
                                                          rset.getInt ("CST_Box_Count")+"",
                                                          StringUtils.toStringArray( rset.getString ("CST_AW_Bill_Number"), ";" ),
                                                          //salesOrder.getShippingMethod() + "-" + salesOrder.getShippingAccount(),
                                                          salesOrder.getShippingMethod() ,
                                                          salesOrder.getShippingMethod(),
                                                          salesOrder.getShippingAccount(),
                                                          salesOrder.getBillingAddress(),
                                                          salesOrder.getShippingAddress(),
                                                          rset.getString ("CST_Shipping_Instructions"));

                packingSlipForm.setBillingAddressId (salesOrder.getBillingAddressId());
                packingSlipForm.setShippingToAddressId (rset.getInt ("CST_TO_Address_ID"));
                packingSlipForm.setShippingFromAddressId (rset.getInt ("CST_From_Address_ID"));
                packingSlipForm.setStatus (rset.getString ("CST_Status"));
                packingSlipForm.setShipDate (rset.getString ("CST_Ship_Date"));

                try
                {
                    weigt = rset.getString( "CST_Weight" );

                    if ( ( weigt == null ) || ( ( weigt != null ) && ( weigt.equalsIgnoreCase( "null" ) ) ) )
                    {
                        weigt = "";
                    }
                    else
                    {
                        weigt = StringUtils.formatAmount( ( float )( new Double( weigt ).doubleValue() ), null );
                    }
                }catch( Exception e ) { weigt = ""; }


                if( weigt != null )
                {
                    packingSlipForm.setWeight( weigt );
                }
                else
                {
                    packingSlipForm.setWeight( "" );
                }

                packingSlipForm.setInvNo( rset.getString ("Inv_No") );
                packingSlipForm.setShippedVia( rset.getString ("CST_Method") );
                packingSlipForm.setShippingAddressObject (salesOrder.getShippingAddressAsObject());
                packingSlipForm.setBillingAddressObject (salesOrder.getBillingAddressAsObject());
                try
                {
                    grossWeight = rset.getString( "CST_Weight" );

                    if( ( grossWeight == null ) || ( ( grossWeight != null ) && ( grossWeight.equalsIgnoreCase( "null" ) ) ) )
                    {
                        grossWeight = "";
                    }
                    else
                    {
                        grossWeight = StringUtils.formatAmount( ( float )( new Double( grossWeight ).doubleValue() ), null );
                    }
                }catch( Exception e ) { grossWeight = ""; }
                if( grossWeight != null )
                {
                    packingSlipForm.setGrossWeight( grossWeight );
                }
                else
                {
                    packingSlipForm.setGrossWeight( "" );
                }
            }

            rset.close();
            rset = null;
            
            pstmt.close();
            pstmt = null;

            // If we changed the shipping method in change order, it will not effect  in Packingslip which is ISSUED.
            // Shipped, Cancelled  EMAIL

            Address address = CustomerDAO.getAddress(packingSlipForm.getShippingToAddressId());
            StringBuffer strBufferAddress = new StringBuffer();
            if(address != null) {
                strBufferAddress = new StringBuffer();
                if((address.getContactPerson()!=null)&&(address.getContactPerson().trim().length()!=0)){
                      strBufferAddress.append(packingSlipForm.getContactPerson()).append(",\n");
                }
                if((address.getAddress1()!=null)&&(address.getAddress1().trim().length()!=0)){
                      strBufferAddress.append(address.getAddress1()).append("\n");
                }
                if((address.getAddress2()!=null)&&(address.getAddress2().trim().length()!=0)){
                     strBufferAddress.append(address.getAddress2()).append("\n");
                }
                if((address.getCity()!=null)&&(address.getCity().trim().length()!=0)){
                     strBufferAddress.append(address.getCity()).append(",");
                }
                if((address.getState()!=null)&&(address.getState().trim().length()!=0)){
                    strBufferAddress.append(address.getState()).append(",");
                }
                if((address.getZipCode()!=null)&&(address.getZipCode().trim().length()!=0)){
                    strBufferAddress.append(address.getZipCode()).append("\n");
                }
                if((address.getCountry()!=null)&&(address.getCountry().trim().length()!=0)){
                    strBufferAddress.append(address.getCountry());
                 }
            }
            packingSlipForm.setShippingAddress(strBufferAddress.toString());
            packingSlipForm.setShippingAddressObject(address);
            if(conn == null){
               conn = DataSource.getConnection();
            }

            // Step 2:Load data from Customer_Shipping_Line_Item Table
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.loadPackingSlipLineItems"));
            pstmt.setInt (1, id);
            rset = pstmt.executeQuery();

            HashMap lineItemMap = new HashMap();
            CustomerShippingLineItem lineItem = null;

            conn1 = DataSource.getConnection();
            pstmt1 = conn1.prepareCall (SqlQueryMappings.get ("operations.packingslip.getPendingQuantityForLineItem"));

            int lineItemId = -1;
            Calendar cal = Calendar.getInstance();

            while (rset.next())
            {
                int customerShipListId = rset.getInt ("CSLIT_ID");
                lineItemId = rset.getInt ("LineItem_ID");
                int qtyShipped = rset.getInt ("CSLIT_Qty_Shipped");
                Date shippedDate = rset.getDate ("CSLIT_Shipped_Date");
                String shippedDateString = "";

                if (shippedDate != null) {
                    cal.setTime (shippedDate);
                    shippedDateString = (cal.get (Calendar.MONTH)+1) + "/" + cal.get (Calendar.DAY_OF_MONTH) + "/" + cal.get (Calendar.YEAR);
                }


                lineItem = new CustomerShippingLineItem (customerShipListId,
                                                            id,
                                                            lineItemId,
                                                            qtyShipped,
                                                            shippedDateString,
                                                            rset.getString ("CSLIT_Description"),
                                                            rset.getString ("CSLIT_SoftwareVersion"),
                                                            rset.getString ("CSLIT_Status"));
                String temp = rset.getString("CSLIT_Cust_Part_Number");
                lineItem.setCustomerPartNumber(temp);
                lineItem.setUnits(rset.getString("CSLIT_Units"));
                // Get pending quantity for this line item
                // Returns the quantity that still is yet to be shipped
                pstmt1.setInt (1, lineItemId);
                pstmt1.registerOutParameter (2, java.sql.Types.INTEGER);

                pstmt1.executeQuery();
                lineItem.setQtyToBeShipped (pstmt1.getInt (2));
                pstmt1.clearParameters();

                lineItemMap.put (lineItemId +"", lineItem);
            }
            
            pstmt1.close();
            pstmt1 = null;

            packingSlipForm.setLineItemMap (lineItemMap);

            rset.close();
            rset =  null;
            pstmt.close();
            pstmt = null;

        }
        catch (Exception e) {
            e.printStackTrace();
            throw new ScalarException ("Could not retrieve PackingSlip from database", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (pstmt1 != null) {
                try { pstmt1.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
            if (conn1 != null) {
                try { conn1.close(); } catch (SQLException ignore) {}
            }
        }

        return packingSlipForm;
    }


    public static ArrayList loadAllPackingSlips (String status) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList packingSlipList = new ArrayList();

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.loadAllPackingSlips"));
            pstmt.setString (1, status);
            rset = pstmt.executeQuery();

            PackingSlipForm packingSlipForm = null;
            while (rset.next()) {
                packingSlipForm = new PackingSlipForm();
                packingSlipForm.setCustomerShippingId (rset.getInt ("CustomerShipping_ID"));
                packingSlipForm.setLabel (rset.getString ("Label"));

                packingSlipList.add (packingSlipForm);
            }
        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve PackingSlip list from database", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return packingSlipList;
    }




    public static void updateCustomerShippingInfo (PackingSlipForm packingSlipForm, List lineItems, HashMap finalInventoryMap, String currentUser) throws ScalarException
    {

        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        String tempWeight       = "";
        String grossWeight      = "";

        try
        {
            try
            {
                if ( ( packingSlipForm.getWeight() != null ) || ( packingSlipForm.getWeight().trim().length() != 0 ) )
                {
                    tempWeight = "" + new Double( packingSlipForm.getWeight() ).doubleValue();
                }
                else
                {
                    tempWeight = null;
                }
            }
            catch( Exception e ) { tempWeight = null; }

            try
            {
                if ( ( packingSlipForm.getGrossWeight() != null ) || ( packingSlipForm.getGrossWeight().trim().length() != 0 ) )
                {
                    grossWeight = "" + new Double( packingSlipForm.getGrossWeight() ).doubleValue();
                }
                else
                {
                    grossWeight = null;
                }
            }
            catch( Exception e ) { grossWeight = null; }
            

            conn = DataSource.getConnection();

            // Step 1:Update Customer_Shipping Table
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.updateCustomerShippingInfo"));

            pstmt.setString (1, packingSlipForm.getContactPerson());
            pstmt.setInt    (2, packingSlipForm.getNoOfBoxesAsInt());
            pstmt.setString (3, packingSlipForm.getTrackingAsString());
            pstmt.setString (4, packingSlipForm.getShipDate());
            pstmt.setString (5, tempWeight);
            pstmt.setString (6, packingSlipForm.getInvNo());
            pstmt.setString (7, currentUser);
            pstmt.setString (8, packingSlipForm.getShippedVia());
            pstmt.setString (9, grossWeight ) ;
            pstmt.setString (10, packingSlipForm.getPackageDimension() );
            pstmt.setString (11, packingSlipForm.getWeightType() );
            pstmt.setInt    (12, packingSlipForm.getCustomerShippingId());


            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;


            // Step 2:Update into Customer_shipping_line_Item table
            if( lineItems != null ) 
            {
                pstmt = conn.prepareStatement( SqlQueryMappings.get ("operations.packingslip.updateLineItems") );

                CustomerShippingLineItem lineItem = null;
                int lineItemCount = lineItems.size();
                int lineItemIdArray[] = new int[lineItemCount];

                for (int i=0; i<lineItemCount; i++) {
                    lineItem = (CustomerShippingLineItem) lineItems.get (i);
                    lineItemIdArray[i] = lineItem.getLineItemId();
                    pstmt.setInt (1, lineItem.getQtyShipped());
                    pstmt.setString (2, lineItem.getShippedDate());
                    pstmt.setString (3, lineItem.getDescription());
                    pstmt.setString (4, lineItem.getUnits());//Bhaskar on 24 Jan 2004
                    pstmt.setString (5, lineItem.getCustomerPartNumber());
                    pstmt.setInt (6, lineItem.getId());

                    pstmt.addBatch();
                }
                pstmt.executeBatch();
                pstmt.close();
                pstmt = null;


            //Bhaskar shifted this step 4 ahead of step 3 on 2 November 2003.
            // Step 4
            // Update 'Balance' and 'Shipping' Fields in Inventory_Lot Table
            if( ( finalInventoryMap != null ) && ( finalInventoryMap.size() != 0 ) )
            {
                pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.updateInventoryLotData" ) );

                Iterator finalInventoryDataListIterator = finalInventoryMap.values().iterator();
                ArrayList finalInventoryDataList = null;
                FinalInventoryData inventoryData = null;

                while( finalInventoryDataListIterator.hasNext() )
                {
                    finalInventoryDataList = ( ArrayList ) finalInventoryDataListIterator.next();

                    if (finalInventoryDataList != null) {
                        int size = finalInventoryDataList.size();

                        for( int i=0; i<size; i++ )
                        {
                            inventoryData = ( FinalInventoryData ) finalInventoryDataList.get( i );
                            int quantityPull = inventoryData.getQuantityPull();
                            int balance = inventoryData.getBalance();
                            if ( quantityPull > balance )
                            {

                            }
                            pstmt.setInt( 1, inventoryData.getLotId() );
                            pstmt.setInt( 2, inventoryData.getBalance() );
                            pstmt.setInt( 3, inventoryData.getCsLineItemId() );
                            pstmt.setInt( 4, inventoryData.getQuantityPull() );
                            pstmt.addBatch();
                        }

                        pstmt.executeBatch();
                        pstmt.clearBatch();
                    }
                }
            }

            //Bhaskar shifted this step 3 to come after step 4 on 2 November 2003.
            // Step 3
                // Update into Customer_Shiping_Lot_Number Table
                if( ( finalInventoryMap != null ) && ( finalInventoryMap.size() != 0 ) )
                {
                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.saveCustomerShippingLotNumberInfo"));

                    int finalInventoryDataListSize = -1;
                    ArrayList finalInventoryDataList = null;
                    FinalInventoryData inventoryData = null;

                    //int cslitId = -1;
                    for( int i=0; i<lineItemIdArray.length; i++ )
                    {
                        finalInventoryDataList = (ArrayList) finalInventoryMap.get (lineItemIdArray[i] + "");

                        if( ( finalInventoryDataList != null ) && ( finalInventoryDataList.isEmpty() != true ) )
                        {
                            finalInventoryDataListSize = finalInventoryDataList.size();

                            for( int j=0; j<finalInventoryDataListSize; j++ )
                            {
                                inventoryData = (FinalInventoryData) finalInventoryDataList.get (j);

                                pstmt.setInt( 1, inventoryData.getCsLineItemId() );
                                pstmt.setInt( 2, inventoryData.getLotId() );
                                pstmt.setInt( 3, inventoryData.getQuantityPull() );

                                pstmt.addBatch();
                            }

                            pstmt.executeBatch();
                            pstmt.clearBatch();
                        }
                    }   //if (finalInventoryDataList != null)

                    pstmt.close();
                    pstmt = null;

                }   //if (finalInventoryMap != null)

            }   //if (lineItems != null)

            success = true;
        }
        catch( Exception sqle )
        {
            throw new ScalarException ("Could not update customer shipping information into database", sqle);
        }
        finally
        {
            try {
                if (conn != null) {
                    if (success == true) {
                        //dbTransaction.commit();
                        conn.commit();//Bhaskar commented on 5 Nov
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    //for updating inv no and airway bill no
    public static void updateCustomerShippingForInvNo (PackingSlipForm packingSlipForm, String updatedBy) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        //ResultSet rset = null;
        boolean success = false;

        try {
            conn = DataSource.getConnection();

            // Step 1
            // Update Customer_Shipping Table
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.updateCustomerShippingForInvNo"));

            pstmt.setString (1, packingSlipForm.getTrackingAsString());
            pstmt.setString (2, packingSlipForm.getInvNo());
            pstmt.setString (3, updatedBy);
            pstmt.setInt (4, packingSlipForm.getCustomerShippingId());

            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
            throw new ScalarException ("Could not update customer shipping information into database", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    public static int saveCustomerShippingInfo (PackingSlipForm packingSlipForm, List lineItems, HashMap finalInventoryMap, String generatedBy, boolean confirmPackingSlip) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        int customerShippingId = -1;
        DBTransaction dbTransaction = null;

        try {

            // changed by hareesh on 21st August 2003
            // -- START --
            //conn = DataSource.getConnection();
            dbTransaction = DBTransaction.getInstance();
            dbTransaction.begin();
            conn = dbTransaction.getConnection();
            // -- END --

            // Step1.1
            // We have ShippingFrom LocationID, get AddressID from it
            // changed by hareesh on 10th October 2003
            // -- START --
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.location.getAddressId"));
            pstmt.setInt (1, packingSlipForm.getShippingFromLocationId());

            rset = pstmt.executeQuery();
            if (rset.next() != false) {
                packingSlipForm.setShippingFromAddressId (rset.getInt ("Address_ID"));
            }

            rset.close();
            pstmt.close();
            pstmt = null;
            // -- END --

            // Step1.1
            // We have ShippingTo Address, and get country and add to commercial invoice desitination port
            String destinationPort = null;
            if( packingSlipForm.getShippingToAddressId() != -1){
                Address shippingAddress = UserDAO.loadAddressById(packingSlipForm.getShippingToAddressId());
                if( shippingAddress.getCountry() != null && shippingAddress.getCountry().trim().length() != 0 ){
                    destinationPort = shippingAddress.getCountry();
                }
            }

            // Step 2
            // Insert Customer Shipping Info
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.insertCustomerShippingInfo"));
            //System.out.println("Shipped VIA: " + packingSlipForm.getShippedVia() );
            pstmt.setString (1, packingSlipForm.getLabel());
            pstmt.setInt (2, packingSlipForm.getSalesOrderId());
            pstmt.setString (3, packingSlipForm.getContactPerson());
            pstmt.setString (4, packingSlipForm.getTrackingAsString());
            pstmt.setString (5, packingSlipForm.getFob());
            pstmt.setInt (6, packingSlipForm.getNoOfBoxesAsInt());
            pstmt.setFloat (7, 0.0f /*shippingcost*/);

            String packingSlipStatus = ((confirmPackingSlip) ? CUSTOMER_SHIPPING_STATUS_SHIPPED : CUSTOMER_SHIPPING_STATUS_ISSED);
            pstmt.setString (8, packingSlipStatus );
            pstmt.setInt (9, packingSlipForm.getShippingFromAddressId());
            pstmt.setInt (10, packingSlipForm.getShippingToAddressId());
            //Added by vasu on 06 June 2003
            //pstmt.setString (11, packingSlipForm.getShippingMethod());
            pstmt.setString (11, packingSlipForm.getShippedVia() );
            pstmt.setString (12, packingSlipForm.getShippingAccount());
            pstmt.setString (13, packingSlipForm.getShippingInstructions());
            pstmt.setString (14, packingSlipForm.getShipDate());
            String tempWeight = "";
            try
            {
                if ( ( packingSlipForm.getWeight() != null ) || (packingSlipForm.getWeight().trim().length() != 0 ))
                    tempWeight = ""+new Double(packingSlipForm.getWeight().trim()).doubleValue();
                else
                    tempWeight = null;
            }
            catch(Exception e)
            {
                tempWeight = null;
            }
            pstmt.setString (15, tempWeight);
            pstmt.setString (16, generatedBy);
            pstmt.setString (17, packingSlipForm.getInvNo());
            /*System.out.println(packingSlipForm.getLabel() + " , " + packingSlipForm.getSalesOrderId() + " , " + packingSlipForm.getContactPerson() + " , " +
            packingSlipForm.getTracking() + " , " +packingSlipForm.getFob()+" , " +packingSlipForm.getNoOfBoxesAsInt() + " , " + packingSlipStatus + " , " +
            packingSlipForm.getShippingFromAddressId()+ " ," +  packingSlipForm.getShippingToAddressId() + " , "+
            packingSlipForm.getShippingMethod() + " , " + packingSlipForm.getShippingAccount() + " , " + packingSlipForm.getShippingInstructions() + " , " +
            packingSlipForm.getShipDate() + " , " +packingSlipForm.getWeight() + " , " +generatedBy+" , " +  packingSlipForm.getInvNo()); */
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;


            // Step 3
            // Get the customerShippingId from customer_shipping table
            /*Bhaskar commented the line below because it has a hard coded
             *sql statement in it and beneath the commented line added a new
             *statement with a that fetches the property from SqlQueryMappings file
             *which inturn is passed in the method. Done on 25 August 2003
             */
            //pstmt = conn.prepareStatement ("SELECT @@IDENTITY as CustomerShipping_ID");
            pstmt = conn.prepareStatement (SqlQueryMappings.get("operations.packingslip.insertCustomerShippingInfo.readIdentity"));
            rset = pstmt.executeQuery();
            if (rset.next() != false) {
                customerShippingId =  rset.getInt ("CustomerShipping_ID");
            }
            rset.close();
            pstmt.close();
            pstmt = null;

            //System.out.println ("customer Shipping ID = " + customerShippingId);

            // Step 4
            // Insert into Customer_shipping_line_Item table
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.saveLineItems"));

            CustomerShippingLineItem lineItem = null;
            int lineItemCount = lineItems.size();

            for (int i=0; i<lineItemCount; i++) {
                lineItem = (CustomerShippingLineItem) lineItems.get (i);

                pstmt.setInt (1, customerShippingId);
                pstmt.setInt (2, lineItem.getLineItemId());
                pstmt.setInt (3, lineItem.getQtyShipped());
                //pstmt.setString (4, lineItem.getShippedDate()); //do we need packingslipForm date
                pstmt.setString (4, packingSlipForm.getShipDate());
                pstmt.setString (5, CUSTOMER_SHIPPING_STATUS_ISSED);
                pstmt.setString (6, lineItem.getDescription());
                pstmt.setString (7, lineItem.getUnits());//Bhaskar on 24 Jan 2004
                pstmt.setString (8, lineItem.getCustomerPartNumber());
                //System.out.println ("cs ID = " + customerShippingId + ", lineItemId = " + lineItem.getLineItemId() + ", qty Shipped = " + lineItem.getQtyShipped() + ", qty shipped date = " + lineItem.getShippedDate());
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            pstmt.close();
            pstmt = null;

            // Step 3.1 and 4.1
            // Changed by hareesh on 21st August 2003
            // -- START --
            // -- to set Commercial Invoice with Client Specific values
            // -- fields such as LOADING_PORT,COUNTRY_OF_ORIGIN,LICENSE_SYMBOL,ECCN of commerical Invoice
            // -- and also SCHEDULE_NUMBER Number of each Line Item in Commercial Invoice
            if(!confirmPackingSlip){ // when confirmPackingSlip is false means it is new Packing Slip.

                String loadingPort =null;
                String countryOfOrigin = null;
                String licenseSymbol = null;
                String eccn = null;
                String scheduleNumber = null;

                // retereving the Cleint Speicfic values of Commerical Invoice
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.commercialinvoice.loadCommercialInvoiceInitialValues"));
                rset = pstmt.executeQuery();
                if (rset.next() != false) {
                    loadingPort = rset.getString("LOADING_PORT");
                    countryOfOrigin = rset.getString("COUNTRY_OF_ORIGIN");
                    licenseSymbol  = rset.getString("LICENSE_SYMBOL");
                    eccn = rset.getString("ECCN");
                    scheduleNumber =rset.getString("SCHEDULE_NUMBER");
                }
                rset.close();
                pstmt.close();
                pstmt = null;

                if (loadingPort != null)loadingPort = loadingPort.trim();
                if (countryOfOrigin != null)countryOfOrigin = countryOfOrigin.trim();
                if (licenseSymbol != null)licenseSymbol = licenseSymbol.trim();
                if (eccn != null)eccn = eccn.trim();
                if (scheduleNumber != null)scheduleNumber = scheduleNumber.trim();

                CommercialInvoiceForm commercialInvoiceForm = new CommercialInvoiceForm();
                commercialInvoiceForm.setPortOfLanding(loadingPort);
                commercialInvoiceForm.setCountryOfOrigin(countryOfOrigin);
                commercialInvoiceForm.setLicenseSymbol(licenseSymbol);
                // changed by hareesh on 10th October 2003
                //commercialInvoiceForm.setPortOfDestination(null);
                if(destinationPort != null && destinationPort.trim().length() != 0  ){
                    commercialInvoiceForm.setPortOfDestination(destinationPort);
                } else {
                    commercialInvoiceForm.setPortOfDestination(null);
                }
                commercialInvoiceForm.setEccn(eccn);
                commercialInvoiceForm.setCustomerShippingId(customerShippingId);

                HashMap lineItemScheduleNumberMap = new HashMap();
                lineItemCount = lineItems.size();
                for (int i=0; i<lineItemCount; i++) {
                    lineItem = (CustomerShippingLineItem) lineItems.get (i);
                    lineItemScheduleNumberMap.put(lineItem.getLineItemId()+"",scheduleNumber);
                 }
                CommercialInvoiceDAO.saveCommercialInvoice(dbTransaction,commercialInvoiceForm,lineItemScheduleNumberMap);
            }
            // -- END --

            // Step 5
            // Get CSLIT_ID from Customer_Shipping_line_item table
            int cslitId = -1;
            /*Bhaskar commented the line below because it has a hard coded
             *sql statement in it and beneath the commented line added a new
             *statement with a that fetches the property from SqlQueryMappings file
             *which inturn is passed in the method. Done on 22 August 2003
             */

            //pstmt = conn.prepareStatement ("SELECT @@IDENTITY as CSLIT_ID");
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.saveCustomerShippingInfo.readIdentity"));
            rset = pstmt.executeQuery();
            if (rset.next() != false) {
                cslitId =  rset.getInt ("CSLIT_ID");
            }
            rset.close();
            pstmt.close();
            pstmt = null;


            if( confirmPackingSlip )    // Insert a row in Customer_Shipped_order_line_item table
            {
                CallableStatement cstmt = conn.prepareCall( SqlQueryMappings.get( "operations.packingslip.insertShippedOrderLineItem" ) );

                cstmt.setInt( 1, customerShippingId );
                cstmt.executeUpdate();

                cstmt.close();
                cstmt = null;
            }

            // Step 6
            // Insert into Customer_Shiping_Lot_Number Table
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.saveCustomerShippingLotNumberInfo" ) );

            int seedCslitId = (cslitId - lineItemCount)+1;

            int finalInventoryDataListSize = -1;
            ArrayList finalInventoryDataList = null;
            FinalInventoryData inventoryData = null;

            for( int i=0; i<lineItemCount; i++, seedCslitId++ )
            {
                finalInventoryDataList = ( ArrayList ) finalInventoryMap.get (((CustomerShippingLineItem) lineItems.get (i)).getLineItemId()+"");

                if( finalInventoryDataList != null )
                {
                    finalInventoryDataListSize = finalInventoryDataList.size();

                    for( int j=0; j<finalInventoryDataListSize; j++ )
                    {
                        inventoryData = (FinalInventoryData) finalInventoryDataList.get (j);

                        if( inventoryData.getQuantityPull() > 0 )  //insert only selected lots (quantity must be greater than zero)
                        {
                            pstmt.setInt (1, seedCslitId);
                            pstmt.setInt (2, inventoryData.getLotId());
                            pstmt.setInt (3, inventoryData.getQuantityPull());

                            //System.out.println ("cslid ID = " + seedCslitId + ", lot Id = " + inventoryData.getLotId() + ", qty pull = " + inventoryData.getQuantityPull());
                            pstmt.addBatch();
                        }
                    }

                    pstmt.executeBatch();
                    pstmt.clearBatch();
                }
            }

            pstmt.close();
            pstmt = null;


            // Step 7
            // Update 'Balance' and 'Shipping' Fields in Inventory_Lot Table
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.updateInventoryLotData"));
            Iterator finalInventoryDataListIterator = finalInventoryMap.values().iterator();

            while (finalInventoryDataListIterator.hasNext()) {
                finalInventoryDataList = (ArrayList) finalInventoryDataListIterator.next();

                if (finalInventoryDataList != null) {
                    int size = finalInventoryDataList.size();

                    for (int i=0; i<size; i++) {
                        inventoryData = (FinalInventoryData) finalInventoryDataList.get (i);

                        pstmt.setInt( 1, inventoryData.getLotId() );
                        pstmt.setInt( 2, inventoryData.getBalance() );
                        pstmt.setInt( 3, inventoryData.getCsLineItemId() );
                        pstmt.setInt( 4, inventoryData.getQuantityPull() );

                        pstmt.addBatch();
                    }

                    pstmt.executeBatch();
                    pstmt.clearBatch();
                }
            }

            pstmt.close();
            pstmt = null;
            conn.close();
            conn = null;
            // Step 8
            // Update the Lot_Reservation_Table Release the lots reserved for the Packing slip
            for (int i=0; i<lineItemCount; i++) {
                lineItem = (CustomerShippingLineItem) lineItems.get (i);
                releaseReservedLots ( dbTransaction , lineItem.getLineItemNumber()+""  , packingSlipForm.getSalesOrderId()+"");
            }

            success = true;
        }
        catch (Exception sqle) {
            throw new ScalarException ("Could not save customer shipping information into database", sqle);
        }
        finally {
            try {
                if(dbTransaction == null){
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                else
                {
                    if (success == true) {
                        dbTransaction.commit();
                    }else{
                        dbTransaction.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return customerShippingId;
    }



    public static void updatePackingSlipStatus (int packingSlipId, String status) throws ScalarException {

        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;

        try {
            conn = DataSource.getConnection();

            cstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.updatePackingSlipStatus"));
            cstmt.setInt (1, packingSlipId);
            cstmt.setString (2, status);
            cstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
            throw new ScalarException ("Could not update the packing slip status for packingslip ID: " + packingSlipId, sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }


    // Added by Balaram... on June 17, 2003
    public static String getPackingSlipNumberById( int customerShippingId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String packingSlipNumber = null;

        boolean success = false;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.getPackingSlipNumberById" ) );
            pstmt.setInt( 1, customerShippingId );
            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                packingSlipNumber = rset.getString( "Packing_Slip_Number" );
            }

            success = true;
        }
        catch( Exception sqle )
        {
            throw new ScalarException ("Could not get Packing Slip Number for Customer Shipping ID: " + customerShippingId, sqle);
        }
        finally
        {
            try
            {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return packingSlipNumber;
    }


    // Added by Balaram, June 17, 2003
    public static String[] getLineItemScheduleNumberAndDescription( int lineItemId, int customerShippingId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String scheduleNumber = null;
        String description = null;
        String cust_part_num = null;

        boolean success = false;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.getLineItemScheduleNumberAndDescription" ) );

            pstmt.setInt( 1, customerShippingId );
            pstmt.setInt( 2, lineItemId );

            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                scheduleNumber = rset.getString( "Schedule_Number" );
                description = rset.getString( "Description" );
                cust_part_num = rset.getString ("Customer_Part_Number");

            }

            success = true;
        }
        catch( Exception sqle )
        {
            throw new ScalarException( "Could not get LineItem Schedule Number for Customer Shipping ID: " + customerShippingId + ", LineItem ID: " + lineItemId, sqle );
        }
        finally
        {
            try
            {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return new String[]{ scheduleNumber, description, cust_part_num };
    }


    public static boolean doesPackingSlipLabelExist (String packingSlipLabel) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;

        if (packingSlipLabel == null) return false;

        boolean labelExists = true;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.doesPackingSlipLabelExist"));
            pstmt.setString (1, packingSlipLabel);
            rset = pstmt.executeQuery();

            if (rset.next()) {
                labelExists = true;
            }
            else {
                labelExists = false;
            }

            success = true;
        }
        catch (Exception sqle) {
            throw new ScalarException ("Could not check whether " + packingSlipLabel + " exists or not", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return labelExists;
    }

    public static ArrayList loadPackingSlipsForSearch (String salesOrderNo,String customerPo,String customerId, String status) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList packingSlipList = new ArrayList();
        String statusCheck = null;//Added by Vasu on 8th may 2003
        String sqlQuery = "";

        try {
            conn = DataSource.getConnection();
            if((status != null) && (status.equalsIgnoreCase("OPEN")))
            {
            /*Bhaskar commented the line below because it has a hard coded
             *sql statement in it and beneath the commented line added a new
             *statement with a that fetches the property from SqlQueryMappings file
             *which inturn is passed in the method. Done on 22 August 2003

             if( salesOrderNo != null && salesOrderNo.length() > 0 )
                salesOrderNo = "'"+salesOrderNo+"'";
             if( customerPo != null && customerPo.length() > 0 )
             customerPo = "'"+customerPo+"'";
             if( status != null && status.length() > 0 )
             status = "'"+status+"'";   */

                sqlQuery = SqlQueryMappings.get ("operations.packingslip.loadPackingSlipsForSearch");
                //pstmt = conn.prepareCall ("select DISTINCT SalesOrder_Id,CustomerShipping_Id, Label,SalesOrder_No,C_Name,SO_Customer_PO_Number,CST_Status,Inv_No from fn_load_all_open_packingslips(?,?,?) ORDER BY SalesOrder_No");
                pstmt = conn.prepareCall (sqlQuery);
                pstmt.setString (1, salesOrderNo);
                pstmt.setString (2, customerPo);
                pstmt.setInt (3, Integer.parseInt(customerId));
            }
            /*
            else if( (salesOrderNo.trim().length() != 0 ) && (status.equals("0")) )
            {System.out.println("2");
                //Added by Vasu on 8th may 2003
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.loadAllStatusPackingSlipsBySalesOrdeNo"));
                pstmt.setString(1,salesOrderNo+"%");
            }
            else if((salesOrderNo != null) && (salesOrderNo.trim().length() > 0) && (!status.equals("0")) )
            {System.out.println("3");
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.loadAllPackingSlipsBySalesOrdeNo"));
                pstmt.setString(1,salesOrderNo+"%");
                pstmt.setString(2,status);
            }

            else if((!status.equals("0")) && (customerPo.trim().length() != 0))
            {System.out.println("4");
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.loadAllPackingSlipsByCustomerPoNo"));
                pstmt.setString(1,customerPo+"%");
                pstmt.setString(2,status);
            }
    else if((!customerId.equals("0")) && (status.equals("0")) )

            {   System.out.println("5");
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.loadAllStatusPackingSlipsByCustomer"));
                pstmt.setInt(1,Integer.parseInt(customerId));

            }
            else if(!customerId.equals("0"))
            {System.out.println("6");
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.loadAllPackingSlipsByCustomer"));
                pstmt.setInt(1,Integer.parseInt(customerId));
                pstmt.setString(2,status);
            }

            */
            else /*if((status.equalsIgnoreCase("0") ) || (status =="0"))*/
            {//System.out.println("7");


                //Added by Vasu on 8th may 2003
               pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.loadAllStatusPackingSlips"));
               //System.out.println("Sales No " + salesOrderNo + " customerPO = " + customerPo + "  customerId = " + customerId  + " Status = " + status);
               pstmt.setString (1, salesOrderNo);
               pstmt.setString (2, customerPo);
               pstmt.setString(3,customerId);
               pstmt.setString (4, status);

            }/*
            else
            {System.out.println("8");
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.loadAllPackingSlips1"));
                pstmt.setString(1,status);
            }*/

            rset = pstmt.executeQuery();

            PackingSlip packingSlip = null;
            int salesOrderId=0;
            while(rset.next())
            {
                //Modified by vasu
                salesOrderId=rset.getInt("SalesOrder_Id");
                int lineItemsCount = SalesOrderDAO.openSalesOrderLineItemCount(salesOrderId);
                if ( lineItemsCount > 0 )
                {
                    packingSlip = new PackingSlip();
                    packingSlip.setCustomerShippingId(rset.getInt("CustomerShipping_Id"));
                    packingSlip.setSalesOrderId(salesOrderId);
                    packingSlip.setLabel(rset.getString("Label"));
                    packingSlip.setSalesOrderNo(rset.getString("SalesOrder_No"));
                    packingSlip.setCustomerName(rset.getString("C_Name"));
                    packingSlip.setCustomerPoNo(rset.getString("SO_Customer_PO_number"));
                    //Added by Vasu on 8th may 2003
                    statusCheck = rset.getString("CST_Status");
                    if ( statusCheck == null)
                    {
                        packingSlip.setStatus("OPEN");
                    }
                    else
                    {
                        packingSlip.setStatus(statusCheck);
                    }
                    packingSlip.setInvNo(rset.getString("Inv_No"));

                    packingSlipList.add(packingSlip);
                }
            }

        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve PackingSlip list from database", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return packingSlipList;
    }

    public static ArrayList loadAllPackingSlipsForSearch (String salesOrderNo,String customerPo,String customerId, String status, boolean dropShipment) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList packingSlipList = new ArrayList();
        String statusCheck = null;//Added by Vasu on 8th may 2003
        String sqlQuery = "";
        try
        {
            conn = DataSource.getConnection();
            if((status != null) && (status.equalsIgnoreCase("OPEN")))
            {
                sqlQuery = SqlQueryMappings.get ("operations.packingslip.loadPackingSlipsForSearchQuery");

                if( salesOrderNo != null && salesOrderNo.trim().length() > 0 )
                {
                    sqlQuery = sqlQuery + " and SalesOrder_No like '" + salesOrderNo + "%' ";
                }
                if( customerPo != null && customerPo.trim().length() > 0 )
                {
                    sqlQuery = sqlQuery + " and SO_Customer_PO_Number like '" + customerPo + "%' ";
                }
                if( !customerId.equals("0") )
                {
                    sqlQuery = sqlQuery + " and Customer.Customer_ID = " + customerId + " ";
                }
                sqlQuery = sqlQuery + " order by SalesOrder_No ";

                //System.out.println("sql query   open    "+sqlQuery);

                pstmt = conn.prepareStatement (sqlQuery);
            }
            else if( (status != null) && ( status.equalsIgnoreCase("ISSUED") || status.equalsIgnoreCase("SHIPPED") || status.equalsIgnoreCase("CANCELLED") ) )
            {
                sqlQuery = SqlQueryMappings.get ("operations.packingslip.loadAllPackingSlipsForSearchQuery");
                if( salesOrderNo != null && salesOrderNo.trim().length() > 0 )
                {
                    sqlQuery = sqlQuery + " and SalesOrder_No like '" + salesOrderNo + "%' ";
                }
                if( customerPo != null && customerPo.trim().length() > 0 )
                {
                    sqlQuery = sqlQuery + " and SO_Customer_PO_Number  like '" + customerPo + "%' ";
                }
                if( !customerId.equals("0") )
                {
                    sqlQuery = sqlQuery + " and Customer.Customer_ID = " + customerId + " ";
                }
                sqlQuery = sqlQuery + " and CST_Status = '" + status + "' ";

                if( status.equalsIgnoreCase("ISSUED") )
                {
                    if( dropShipment )
                        sqlQuery = sqlQuery + " and CST_IS_Drop_Shipment = 0";
                    else
                        sqlQuery = sqlQuery + " and ( CST_IS_Drop_Shipment != 0 or CST_IS_Drop_Shipment is null )";
                }

                sqlQuery = sqlQuery + " order by SalesOrder_No, customershipping_id ";

                //System.out.println("sql query   "+sqlQuery);
                pstmt = conn.prepareStatement (sqlQuery);
            }
            else
            {
                sqlQuery = SqlQueryMappings.get ("operations.packingslip.loadPackingSlipsForSearchQuery");

                if( salesOrderNo != null && salesOrderNo.trim().length() > 0 )
                {
                    sqlQuery = sqlQuery + " and SalesOrder_No like '" + salesOrderNo + "%' ";
                }
                if( customerPo != null && customerPo.trim().length() > 0 )
                {
                    sqlQuery = sqlQuery + " and SO_Customer_PO_Number  like '" + customerPo + "%' ";
                }
                if( !customerId.equals("0") )
                {
                    sqlQuery = sqlQuery + " and Customer.Customer_ID = " + customerId + " ";
                }
                sqlQuery = sqlQuery + " union all ";
                sqlQuery = sqlQuery + SqlQueryMappings.get ("operations.packingslip.loadAllPackingSlipsForSearchQuery");
                if( salesOrderNo != null && salesOrderNo.trim().length() > 0 )
                {
                    sqlQuery = sqlQuery + " and SalesOrder_No like '" + salesOrderNo + "%' ";
                }
                if( customerPo != null && customerPo.trim().length() > 0 )
                {
                    sqlQuery = sqlQuery + " and SO_Customer_PO_Number  like '" + customerPo + "%' ";
                }
                if( !customerId.equals("0") )
                {
                    sqlQuery = sqlQuery + " and Customer.Customer_ID = " + customerId + " ";
                }

                if( dropShipment ) // Only Open and Issued Packing slips should Appear for Drop shipment
                    sqlQuery = sqlQuery + " and CST_IS_Drop_Shipment = 0 and CST_IS_Drop_Shipment =0 and CST_Status = 'ISSUED'";
                else
                    sqlQuery = sqlQuery + " and ( ((CST_IS_Drop_Shipment != 0 or CST_IS_Drop_Shipment is null) and  CST_Status = 'ISSUED') or CST_Status = 'CANCELLED' or CST_Status = 'SHIPPED' )";

                sqlQuery = sqlQuery + " order by SalesOrder_No ";
                pstmt = conn.prepareStatement (sqlQuery);
            }

            rset = pstmt.executeQuery();

            PackingSlip packingSlip = null;
            int salesOrderId=0;

            while(rset.next())
            {
                salesOrderId=rset.getInt("SalesOrder_Id");
                int lineItemsCount = SalesOrderDAO.openSalesOrderLineItemCount(salesOrderId);
                if ( lineItemsCount > 0 )
                {
                    packingSlip = new PackingSlip();
                    packingSlip.setCustomerShippingId(rset.getInt("CustomerShipping_Id"));
                    packingSlip.setSalesOrderId(salesOrderId);
                    packingSlip.setLabel(rset.getString("Label"));
                    packingSlip.setSalesOrderNo(rset.getString("SalesOrder_No"));
                    packingSlip.setCustomerName(rset.getString("C_Name"));
                    packingSlip.setCustomerPoNo(rset.getString("SO_Customer_PO_number"));
                    statusCheck = rset.getString("CST_Status");
                    if ( statusCheck == null)
                    {
                        packingSlip.setStatus("OPEN");
                    }
                    else
                    {
                        packingSlip.setStatus(statusCheck);
                    }
                    packingSlip.setInvNo(rset.getString("Inv_No"));

                    packingSlipList.add(packingSlip);
                }
            }

        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve PackingSlip list from database", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return packingSlipList;
    }

/**
 * This method is used to search the status of given sales order number
 * author vasu
 * date 10th June 2003
 **/
public static HashMap getLineItemStatusBySalesOrderId ( String salesOrderNo ) throws ScalarException
{

    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rset = null;

    HashMap coolStatusMap = new HashMap();

    try
    {
        conn = DataSource.getConnection();
        if( salesOrderNo != null )
        {
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.getLineItemStatusBySalesOrderId"));
            pstmt.setString (1, salesOrderNo);
        }

        rset = pstmt.executeQuery();

        while(rset.next())
        {
            coolStatusMap.put( rset.getInt( "LineItem_ID" )+"", rset.getString( "COOL_Status" ) );
        }
    }
    catch( Exception e )
    {
        throw new ScalarException ("Could not retrieve LineItem status for SalesOrder " + salesOrderNo, e);
    }
    finally
    {
        if (rset != null) {
            try { rset.close(); } catch (SQLException ignore) {}
        }
        if (pstmt != null) {
            try { pstmt.close(); } catch (SQLException ignore) {}
        }
        if (conn != null) {
            try { conn.close(); } catch (SQLException ignore) {}
        }
    }

    return coolStatusMap;
}

    /**
    * Load all the order line items to show
    * @param salesOrderId
    * @return LineItems (HashMap, with lineItemId as the key)
    * moved from SalesOrder.DAO on 27th May 2003 by vasu
    *
    * **flag is introduced such that for issued and shipped packing slips we should not
    * consider the condition COOL_STATUS.
    */
   public static HashMap loadOpenOrderLineItemMap (int salesOrderId, int flag) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        HashMap lineItems = new LinkedHashMap();

        if(salesOrderId == -1)
            return null;

        try {
            conn = DataSource.getConnection();
            //Load all the open order lineItems
            if( flag ==0 )
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadOpenOrderLineItems"));
            else
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadOpenOrderLineItemsWithHold"));

            pstmt.setInt (1, salesOrderId);

            rs = pstmt.executeQuery();
            LineItem lineItem = null;
            LineItem finalLineItem = null;
            ArrayList finalInventoryList = null;
            int lineItemId = -1;

            while(rs.next()) {
                lineItem = new LineItem();
                lineItemId = rs.getInt("LineItem_ID");
                lineItem.setLineItemId(lineItemId);
                lineItem.setLineItemNo(rs.getInt("COOL_Line_Item_No"));

            // added by ravikanth on 20 Aug 2003 to show PO Line Item in mail and Printer Friendly version
                if( rs.getInt("COOL_PO_Line_Item_No") == 0 )
                {
                    lineItem.setPoLineItemNo(0);
                }
                else
                {
                    lineItem.setPoLineItemNo(rs.getInt("COOL_PO_Line_Item_No"));
                }

                lineItem.setProductId(rs.getInt("Product_ID"));
                lineItem.setQuantity(rs.getInt("COOL_Qty"));
                lineItem.setUnitPrice(rs.getFloat("COOL_Unit_Cost"));
                lineItem.setAmount(rs.getFloat("COOL_Amount"));
                lineItem.setRequestDate(StringUtils.formatDateToString(rs.getDate("COOL_Requested_Date"),null));
                lineItem.setSuggScheduleDateWithSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_With_Substitutables"),null));
                lineItem.setSuggScheduleDateWithoutSubstitutables(StringUtils.formatDateToString(rs.getDate("COOL_Sugg_Schedule_Date_Without_Substitutables"),null));
                lineItem.setScheduleDate(StringUtils.formatDateToString(rs.getDate("COOL_Schedule_Date"),null));
                lineItem.setRevisedDate(StringUtils.formatDateToString(rs.getDate("COOL_Revised_Date"),null));
                lineItem.setStatus(rs.getString("COOL_Status"));
                lineItem.setDescription(rs.getString("COOL_Description"));

                String suggDate = lineItem.getSuggScheduleDateWithoutSubstitutables();
                if ((suggDate == null) || (suggDate.trim().length() == 0)) {
                    suggDate = lineItem.getScheduleDate();
                }

                lineItem.setSuggScheduleDate (suggDate);

                String scheduleDate = lineItem.getScheduleDate();
                if ((scheduleDate == null) || (scheduleDate.trim().length() == 0)) {
                    lineItem.setScheduleDate (suggDate);
                }
                finalLineItem = new LineItem();
                finalLineItem = getMarketingAndMfgPartNumbers(lineItem);
                finalInventoryList = new ArrayList ();
                finalInventoryList = FinalInventoryDAO.loadFinalInventoryDataByPartNumberAndLineItem( finalLineItem.getManufacturingPartNo(), finalLineItem.getLineItemId()+"", "-1" );
                int inventorySize = 0;
                if (( finalInventoryList !=null ) && ( finalInventoryList.size() != 0 ) )
                {
                    inventorySize = finalInventoryList.size();
                    finalLineItem.setInventorySize( inventorySize );
                    //System.out.println("inventorySize: " + inventorySize );
                }
                else
                {
                    finalLineItem.setInventorySize( inventorySize );
                }
                //System.out.println("inventorySize: " + inventorySize );
                //Add the lineItem
                lineItems.put ( lineItemId+"", finalLineItem );
            }


        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get present lineItems Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return lineItems;
   }
    /**
     * Load the Marketing Part Number & Mfg Part Number
     * @param lineItem
     * @return LineItem
     */
    public static LineItem getMarketingAndMfgPartNumbers(LineItem lineItem)
                     throws ScalarException
    {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;

         if(lineItem == null)
             return null;

         try {
             conn = DataSource.getConnection();
             pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.loadMarketingAndMfgPartNumbers"));
             pstmt.setInt (1, lineItem.getLineItemId());

             rs = pstmt.executeQuery();

             while(rs.next()) {
                 lineItem.setManufacturingPartNo(rs.getString("Part_Number"));
                 lineItem.setMarketingPartNo(rs.getString("P_Marketing_Part_Number"));
             }

         }
         catch (Exception sqlex) {
             sqlex.printStackTrace();
             throw new ScalarException ("Could not get part number for this product", sqlex);
         }
         finally {
             if (rs != null) {
                 try { rs.close(); } catch (SQLException excp) {}
             }
             if (pstmt != null) {
                 try { pstmt.close(); } catch (SQLException excp) {}
             }
             if (conn != null) {
                 try { conn.close(); } catch (SQLException excp) {}
             }
         }

         return lineItem;
    }


    public static void loadCustomerPartNumber(HashMap customerShippingLineItemMap, HashMap orderLineItemMap, int customerId ) throws ScalarException
    {
        Iterator it = customerShippingLineItemMap.keySet().iterator();

        while( it.hasNext() )
        {
            String lineItemID = (String)it.next();
            String customerPartNumber = "";
            CustomerShippingLineItem cslt = (CustomerShippingLineItem)customerShippingLineItemMap.get( lineItemID );
            //System.out.println("Quntity to be shipped "+ cslt.getQtyToBeShipped());
            //System.out.println("Line item id "+ cslt.getLineItemId() );

            LineItem li = (LineItem)orderLineItemMap.get( lineItemID );
            customerPartNumber = getCustomerPartNumber( li.getProductId(), customerId );

            if( cslt == null )
            {
                cslt = new CustomerShippingLineItem();
                cslt.setLineItemId( Integer.parseInt( lineItemID ) );
                cslt.setCustomerPartNumber( customerPartNumber );
                cslt.setQtyToBeShipped( li.getQuantity() );
                customerShippingLineItemMap.put( lineItemID , cslt );
            }
            else
            {
                cslt.setCustomerPartNumber( customerPartNumber );
            }
            // cslt.setCustomerPartNumber( getCustomerPartNumber( li.getProductId(), customerId ) );
        }
    }

    private static String getCustomerPartNumber(int productId , int customerId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String custPartNumber = "";

        try {
            conn = DataSource.getConnection();
            //pstmt = conn.prepareStatement ("select Customer_PartNumber from Customer_PartNumbers where Customer_id = ? and Product_Id = ?");
            pstmt = conn.prepareStatement (SqlQueryMappings.get("operations.packingslip.getCustomerPartNumber"));
            pstmt.setInt ( 1, customerId );
            pstmt.setInt ( 2, productId );
            rs = pstmt.executeQuery();

            if( rs.next() )
            {
                custPartNumber = rs.getString( "Customer_PartNumber" );
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Customer part number for this product", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return custPartNumber;
    }

    public static void releaseReservedLots (DBTransaction dbTransaction, String lineItemNumberStr, String salesOrderIdStr) throws ScalarException {

         Connection conn = null;
         PreparedStatement pstmt = null;
         boolean success = false;


         try {

             int lineItemNumber = Integer.parseInt (lineItemNumberStr);
             int salesOrderId = Integer.parseInt (salesOrderIdStr);

             if(dbTransaction == null) {
                 conn = DataSource.getConnection();
             }
             else {
                 conn = dbTransaction.getConnection();
             }

             pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.packingslip.releaseReservedLots"));
             pstmt.setInt (1, lineItemNumber);
             pstmt.setInt (2, salesOrderId);

             pstmt.execute();

             success = true;
         }

         catch (Exception sqle) {
             sqle.printStackTrace();
             throw new ScalarException ("Could not release reserved Lots for lineItem# " + lineItemNumberStr + ", salesOrder ID: " + salesOrderIdStr, sqle);
         }

         finally {

             if (dbTransaction == null) {
                 try {
                     if (conn != null) {
                         conn.commit();
                     }
                 }
                 catch (SQLException ignore) {}
             }

             if (pstmt != null) {
                 try { pstmt.close(); } catch (SQLException ignore) {}
             }

             if (conn != null) {
                 try { conn.close(); } catch (SQLException ignore) {}
             }
         }
     }

    /*
    *Bhaskar added this method on 23 Dec 2003 inorder to commit a drop shipment packing slip directly
    *with shipped status in the respective tables viz. Customer_shipping_table, Customer_shipping_line_item_table,
    *customer_shipped_lot_table, customer_shipped_lot_number_table.
    */
    public static int saveDropShipmentCustomerShippingInfo (PackingSlipForm dropShipmentPSForm, List lineItems, HashMap finalInventoryMap, String generatedBy, boolean confirmPackingSlip) throws ScalarException
    {
        int IS_DROP_SHIPMENT = 0;
        /*
        This hard coded variable is defined by Bhaskar after discussion with Hareesh and RaviKanth on
        3 Jan 2004. This is a key value that will allow the packing slip to be stored successfully.

        int JSI_LOCATION_ID = 14;*/
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        int customerShippingId = -1;
        DBTransaction dbTransaction = null;
        try
        {
            dbTransaction = DBTransaction.getInstance();
            dbTransaction.begin();
            conn = dbTransaction.getConnection();

            // Step1.1
            // We have ShippingFrom LocationID, get AddressID from it
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.location.getAddressId"));
            pstmt.setInt (1, dropShipmentPSForm.getShippingFromLocationId());
            rset = pstmt.executeQuery();
            if (rset.next() != false)
            {
                dropShipmentPSForm.setShippingFromAddressId (rset.getInt ("Address_ID"));
            }
            rset.close();
            pstmt.close();
            pstmt = null;
            // -- END --

            // Step1.2
            // We have ShippingTo Address, and get country and add to commercial invoice desitination port
            String destinationPort = null;
            if( dropShipmentPSForm.getShippingToAddressId() != -1 )
            {
                Address shippingAddress = UserDAO.loadAddressById(dropShipmentPSForm.getShippingToAddressId());
                if( shippingAddress.getCountry() != null && shippingAddress.getCountry().trim().length() != 0 )
                {
                    destinationPort = shippingAddress.getCountry();
                }
            }

            // Step 2
            // Insert Customer Shipping Info
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.dropshipment.insertCustomerShippingInfo"));
            pstmt.setString (1, dropShipmentPSForm.getLabel());
            pstmt.setInt (2, dropShipmentPSForm.getSalesOrderId());
            pstmt.setString (3, dropShipmentPSForm.getContactPerson());
            pstmt.setString (4, dropShipmentPSForm.getTrackingAsString());
            pstmt.setString (5, dropShipmentPSForm.getFob());
            pstmt.setInt (6, dropShipmentPSForm.getNoOfBoxesAsInt());
            pstmt.setFloat (7, 0.0f /*shippingcost*/);
            pstmt.setString (8, CUSTOMER_SHIPPING_STATUS_ISSED);
            pstmt.setInt (9, dropShipmentPSForm.getShippingFromAddressId());
            pstmt.setInt (10, dropShipmentPSForm.getShippingToAddressId());
            pstmt.setString (11, dropShipmentPSForm.getShippedVia() );
            pstmt.setString (12, dropShipmentPSForm.getShippingAccount());
            pstmt.setString (13, dropShipmentPSForm.getShippingInstructions());
            pstmt.setString (14, dropShipmentPSForm.getShipDate());

            String tempWeight = "";
            try
            {
                if ( ( dropShipmentPSForm.getWeight() != null ) || (dropShipmentPSForm.getWeight().trim().length() != 0 ))
                    tempWeight = ""+new Double(dropShipmentPSForm.getWeight().trim()).doubleValue();
                else
                    tempWeight = null;
            }
            catch(Exception e)
            {
                tempWeight = null;
            }
            pstmt.setString (15, tempWeight);
            pstmt.setString (16, generatedBy);
            pstmt.setString (17, dropShipmentPSForm.getInvNo());
            pstmt.setInt (18, IS_DROP_SHIPMENT); //directly a value of 0 is inserted in case of drop shipment.
            /*System.out.println(dropShipmentPSForm.getLabel() + " , " + dropShipmentPSForm.getSalesOrderId() + " , " + dropShipmentPSForm.getContactPerson() + " , " +
            dropShipmentPSForm.getTracking() + " , " +dropShipmentPSForm.getFob()+" , " +dropShipmentPSForm.getNoOfBoxesAsInt() + " , " + packingSlipStatus + " , " +
            dropShipmentPSForm.getShippingFromAddressId()+ " ," +  dropShipmentPSForm.getShippingToAddressId() + " , "+
            dropShipmentPSForm.getShippingMethod() + " , " + dropShipmentPSForm.getShippingAccount() + " , " + dropShipmentPSForm.getShippingInstructions() + " , " +
            dropShipmentPSForm.getShipDate() + " , " +dropShipmentPSForm.getWeight() + " , " +generatedBy+" , " +  dropShipmentPSForm.getInvNo()); */
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;


            // Step 3
            // Get the customerShippingId from customer_shipping_table
            pstmt = conn.prepareStatement (SqlQueryMappings.get("operations.packingslip.insertCustomerShippingInfo.readIdentity"));
            rset = pstmt.executeQuery();
            if (rset.next() != false) {
                customerShippingId =  rset.getInt ("CustomerShipping_ID");
            }
            rset.close();
            pstmt.close();
            pstmt = null;

            // Step 4
            // Insert into Customer_shipping_line_Item_table
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.dropshipment.saveLineItems"));
            CustomerShippingLineItem lineItem = null;
            int lineItemCount = lineItems.size();
            for (int i=0; i<lineItemCount; i++)
            {
                lineItem = (CustomerShippingLineItem) lineItems.get (i);
                pstmt.setInt (1, customerShippingId);
                pstmt.setInt (2, lineItem.getLineItemId());
                pstmt.setInt (3, lineItem.getQtyShipped());
                pstmt.setString (4, dropShipmentPSForm.getShipDate());
                pstmt.setString (5, CUSTOMER_SHIPPING_STATUS_ISSED);
                pstmt.setString (6, lineItem.getDescription());
                pstmt.setString (7, lineItem.getUnits());
                pstmt.setString (8, lineItem.getCustomerPartNumber());
                pstmt.setInt (9, IS_DROP_SHIPMENT);
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            pstmt.close();
            pstmt = null;

            // Step 3.1 and 4.1
            // -- to set Commercial Invoice with Client Specific values
            // -- fields such as LOADING_PORT,COUNTRY_OF_ORIGIN,LICENSE_SYMBOL,ECCN of commerical Invoice
            // -- and also SCHEDULE_NUMBER Number of each Line Item in Commercial Invoice

            String loadingPort =null;
            String countryOfOrigin = null;
            String licenseSymbol = null;
            String eccn = null;
            String scheduleNumber = null;

            // retrieving the Client Speicfic values of Commerical Invoice
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.commercialinvoice.loadCommercialInvoiceInitialValues"));
            rset = pstmt.executeQuery();
            if (rset.next() != false) {
                loadingPort = rset.getString("LOADING_PORT");
                countryOfOrigin = rset.getString("COUNTRY_OF_ORIGIN");
                licenseSymbol  = rset.getString("LICENSE_SYMBOL");
                eccn = rset.getString("ECCN");
                scheduleNumber =rset.getString("SCHEDULE_NUMBER");
            }
            rset.close();
            pstmt.close();
            pstmt = null;

            if (loadingPort != null)loadingPort = loadingPort.trim();
            if (countryOfOrigin != null)countryOfOrigin = countryOfOrigin.trim();
            if (licenseSymbol != null)licenseSymbol = licenseSymbol.trim();
            if (eccn != null)eccn = eccn.trim();
            if (scheduleNumber != null)scheduleNumber = scheduleNumber.trim();

            CommercialInvoiceForm commercialInvoiceForm = new CommercialInvoiceForm();
            commercialInvoiceForm.setPortOfLanding(loadingPort);
            commercialInvoiceForm.setCountryOfOrigin(countryOfOrigin);
            commercialInvoiceForm.setLicenseSymbol(licenseSymbol);
            if(destinationPort != null && destinationPort.trim().length() != 0  )
            {
                commercialInvoiceForm.setPortOfDestination(destinationPort);
            }
            else
            {
                commercialInvoiceForm.setPortOfDestination(null);
            }
            commercialInvoiceForm.setEccn(eccn);
            commercialInvoiceForm.setCustomerShippingId(customerShippingId);

            HashMap lineItemScheduleNumberMap = new HashMap();
            lineItemCount = lineItems.size();
            for (int i=0; i<lineItemCount; i++)
            {
                lineItem = (CustomerShippingLineItem) lineItems.get (i);
                lineItemScheduleNumberMap.put(lineItem.getLineItemId()+"",scheduleNumber);
            }
            CommercialInvoiceDAO.saveCommercialInvoice(dbTransaction,commercialInvoiceForm,lineItemScheduleNumberMap);

            // Step 5
            // Get CSLIT_ID from Customer_Shipping_line_item_table
            /*int cslitId = -1;
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.saveCustomerShippingInfo.readIdentity"));
            rset = pstmt.executeQuery();
            if (rset.next() != false)
            {
                cslitId =  rset.getInt ("CSLIT_ID");
            }
            rset.close();
            pstmt.close();
            pstmt = null;

            // Step 6
            //Insert a row in Customer_Shipped_order_line_item table
            CallableStatement cstmt = conn.prepareCall( SqlQueryMappings.get( "operations.packingslip.insertShippedOrderLineItem" ) );
            cstmt.setInt( 1, customerShippingId );
            cstmt.executeUpdate();
            cstmt.close();
            cstmt = null;*/

            // Step 7
            // Insert into Customer_Shiping_Lot_Number Table change this query
            /*pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.dropshipment.saveCustomerShippingLotNumberInfo" ) );
            int seedCslitId = (cslitId - lineItemCount)+1;
            int finalInventoryDataListSize = -1;
            ArrayList finalInventoryDataList = null;
            FinalInventoryData inventoryData = null;
            for( int i=0; i<lineItemCount; i++, seedCslitId++ )
            {
                finalInventoryDataList = ( ArrayList ) finalInventoryMap.get (((CustomerShippingLineItem) lineItems.get (i)).getLineItemId()+"");
                if( finalInventoryDataList != null )
                {
                    finalInventoryDataListSize = finalInventoryDataList.size();
                    for( int j=0; j<finalInventoryDataListSize; j++ )
                    {
                        inventoryData = (FinalInventoryData) finalInventoryDataList.get (j);
                        if( inventoryData.getQuantityPull() > 0 )  //insert only selected lots (quantity must be greater than zero)
                        {
                            pstmt.setInt (1, seedCslitId);
                            pstmt.setInt (2, inventoryData.getLotId());
                            pstmt.setInt (3, inventoryData.getQuantityPull());
                            pstmt.setString (4, CUSTOMER_SHIPPING_STATUS_SHIPPED);
                            pstmt.addBatch();
                        }
                    }
                    pstmt.executeBatch();
                    pstmt.clearBatch();
                }
            }

            pstmt.close();
            pstmt = null;*/


            /* Step 8
            // Update 'Balance' and 'Shipping' Fields in Inventory_Lot Table
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.updateInventoryLotData"));
            Iterator finalInventoryDataListIterator = finalInventoryMap.values().iterator();

            while (finalInventoryDataListIterator.hasNext()) {
                finalInventoryDataList = (ArrayList) finalInventoryDataListIterator.next();

                if (finalInventoryDataList != null) {
                    int size = finalInventoryDataList.size();

                    for (int i=0; i<size; i++) {
                        inventoryData = (FinalInventoryData) finalInventoryDataList.get (i);

                        pstmt.setInt ( 1, inventoryData.getLotId() );
                        pstmt.setInt ( 2, inventoryData.getBalance() );
                        pstmt.setInt ( 3, inventoryData.getCsLineItemId() );
                        pstmt.setInt ( 4, inventoryData.getQuantityPull() );

                        pstmt.addBatch();
                    }

                    pstmt.executeBatch();
                    pstmt.clearBatch();
                }
            }*/


            //conn.close();

            // Step 8 commented as per discussion with Ravi Kanth on 7 Jan 2004
            // Update the Lot_Reservation_Table Release the lots reserved for the Packing slip
            /*for (int i=0; i<lineItemCount; i++) {
                lineItem = (CustomerShippingLineItem) lineItems.get (i);
                releaseReservedLots ( dbTransaction , lineItem.getLineItemNumber()+""  , packingSlipForm.getSalesOrderId()+"");
            }*/


            success = true;
        }
        catch (Exception sqle)
        {
            throw new ScalarException ("Could not save customer shipping information into database", sqle);
        }
        finally
        {
            try
            {
                if(dbTransaction == null)
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                if (success == true)
                {
                    dbTransaction.commit();
                }
                else
                {
                    dbTransaction.rollback();
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return customerShippingId;
    }



    public static void updateDropShipmentCustomerShippingInfo (PackingSlipForm packingSlipForm, List lineItems, HashMap finalInventoryMap, String currentUser) throws ScalarException {

        int IS_DROP_SHIPMENT = 0;
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;


        try
        {
            conn = DataSource.getConnection();

            // Step 1
            // Update Customer_Shipping Table
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.dropshipment.updateCustomerShippingInfo"));

            pstmt.setString (1, packingSlipForm.getContactPerson());
            pstmt.setInt (2, packingSlipForm.getNoOfBoxesAsInt());
            pstmt.setString (3, packingSlipForm.getTrackingAsString());
            pstmt.setString (4, packingSlipForm.getShipDate());
            String tempWeight = "";
            try
            {
                if ( ( packingSlipForm.getWeight() != null ) || (packingSlipForm.getWeight().trim().length() != 0 ))
                    tempWeight = ""+new Double(packingSlipForm.getWeight().trim()).doubleValue();
                else
                    tempWeight = null;
            }
            catch(Exception e)
            {
                tempWeight = null;
            }
            pstmt.setString (5, tempWeight);
            pstmt.setString (6, packingSlipForm.getInvNo());
            pstmt.setString (7, currentUser);
            pstmt.setString (8, packingSlipForm.getShippedVia());
            pstmt.setString (9, CUSTOMER_SHIPPING_STATUS_SHIPPED);
            pstmt.setInt (10, packingSlipForm.getCustomerShippingId());



            pstmt.executeUpdate();//Bhaskar commented on 5 Nov
            pstmt.close();
            pstmt = null;


            // Step 2
            // Update into Customer_shipping_line_Item table
            if (lineItems != null)
            {
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.dropshipment.updateLineItems"));

                CustomerShippingLineItem lineItem = null;
                int lineItemCount = lineItems.size();
                int lineItemIdArray[] = new int[lineItemCount];

                for (int i=0; i<lineItemCount; i++)
                {
                    lineItem = (CustomerShippingLineItem) lineItems.get (i);
                    lineItemIdArray[i] = lineItem.getLineItemId();
                    pstmt.setInt (1, lineItem.getQtyShipped());
                    pstmt.setString (2, lineItem.getShippedDate());
                    pstmt.setString (3, lineItem.getDescription());
                    pstmt.setString (4, lineItem.getUnits());
                    pstmt.setString (5, lineItem.getCustomerPartNumber());
                    pstmt.setString (6, CUSTOMER_SHIPPING_STATUS_SHIPPED);
                    pstmt.setInt (7, lineItem.getId());

                    pstmt.addBatch();
                }
                pstmt.executeBatch();
                pstmt.close();
                pstmt = null;


                // Step 3
                //Insert a row in Customer_Shipped_order_line_item table
                CallableStatement cstmt = conn.prepareCall( SqlQueryMappings.get( "operations.packingslip.insertShippedOrderLineItem" ) );
                cstmt.setInt( 1, packingSlipForm.getCustomerShippingId() );
                cstmt.executeUpdate();
                cstmt.close();
                cstmt = null;


                // Step 4
                // Insert into Customer_Shiping_Lot_Number Table change this query
                pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.dropshipment.saveCustomerShippingLotNumberInfo" ) );
                //int seedCslitId = (cslitId - lineItemCount)+1;
                int finalInventoryDataListSize = -1;
                ArrayList finalInventoryDataList = new ArrayList();
                FinalInventoryData inventoryData = null;
                for( int i=0; i<lineItemCount; i++ )
                {
                    int lineItmId = ((CustomerShippingLineItem) lineItems.get (i)).getLineItemId();
                    finalInventoryDataList = ( ArrayList ) finalInventoryMap.get (lineItmId+"");
                    if( finalInventoryDataList != null )
                    {
                        finalInventoryDataListSize = finalInventoryDataList.size();
                        for( int j=0; j<finalInventoryDataListSize; j++ )
                        {
                            inventoryData = (FinalInventoryData) finalInventoryDataList.get (j);
                            if( inventoryData.getQuantityPull() > 0 )  //insert only selected lots (quantity must be greater than zero)
                            {
                                pstmt.setInt (1, inventoryData.getLotId());
                                pstmt.setString(2, inventoryData.getLotNumber());
                                pstmt.setString(3, inventoryData.getMktPartNumber());
                                pstmt.setInt(4, inventoryData.getLocationId());
                                pstmt.setInt (5, inventoryData.getQuantityPull());
                                pstmt.setInt (6, inventoryData.getCsLineItemId() );
                                pstmt.setString (7, CUSTOMER_SHIPPING_STATUS_SHIPPED);
                                pstmt.setInt (8, IS_DROP_SHIPMENT );
                                pstmt.addBatch();
                            }
                        }
                        pstmt.executeBatch();
                        pstmt.clearBatch();
                    }
                }

                pstmt.close();
                pstmt = null;


                // Update status in Customer_Open_Order_Line_Item table
                cstmt = conn.prepareCall (SqlQueryMappings.get ("operations.packingslip.updateOpenOrderLineItemStatus"));
                cstmt.setInt (1, packingSlipForm.getCustomerShippingId());
                cstmt.setString ( 2, CUSTOMER_SHIPPING_STATUS_SHIPPED );
                cstmt.setString (3, currentUser);  // added by hareesh on 7th July 2003
                cstmt.executeUpdate();

                // Step 5
                // Update 'Balance' and 'Shipping' Fields in Inventory_Lot Table
                /*if( ( finalInventoryMap != null ) && ( finalInventoryMap.size() != 0 ) )
                {
                    pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.packingslip.updateInventoryLotData" ) );

                    Iterator finalInventoryDataListIterator = finalInventoryMap.values().iterator();
                    ArrayList finalInventoryDataList = null;
                    FinalInventoryData inventoryData = null;

                    while( finalInventoryDataListIterator.hasNext() )
                    {
                        finalInventoryDataList = ( ArrayList ) finalInventoryDataListIterator.next();

                        if (finalInventoryDataList != null) {
                            int size = finalInventoryDataList.size();

                            for( int i=0; i<size; i++ )
                            {
                                inventoryData = ( FinalInventoryData ) finalInventoryDataList.get( i );
                                int quantityPull = inventoryData.getQuantityPull();
                                int balance = inventoryData.getBalance();
                                if ( quantityPull > balance )
                                {

                                }
                                pstmt.setInt( 1, inventoryData.getLotId() );
                                pstmt.setInt( 2, inventoryData.getBalance() );
                                pstmt.setInt( 3, inventoryData.getCsLineItemId() );
                                pstmt.setInt( 4, inventoryData.getQuantityPull() );
                                pstmt.addBatch();
                            }

                            pstmt.executeBatch();
                            pstmt.clearBatch();
                        }
                    }
                }*/



                //Bhaskar shifted this step 3 to come after step 4 on 2 November 2003.
                // Step 3
                // Update into Customer_Shiping_Lot_Number Table
                /*if( ( finalInventoryMap != null ) && ( finalInventoryMap.size() != 0 ) )
                {
                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.saveCustomerShippingLotNumberInfo"));

                    int finalInventoryDataListSize = -1;
                    ArrayList finalInventoryDataList = null;
                    FinalInventoryData inventoryData = null;

                    //int cslitId = -1;
                    for( int i=0; i<lineItemIdArray.length; i++ )
                    {
                        finalInventoryDataList = (ArrayList) finalInventoryMap.get (lineItemIdArray[i] + "");

                        if( ( finalInventoryDataList != null ) && ( finalInventoryDataList.isEmpty() != true ) )
                        {
                            finalInventoryDataListSize = finalInventoryDataList.size();

                            for( int j=0; j<finalInventoryDataListSize; j++ )
                            {
                                inventoryData = (FinalInventoryData) finalInventoryDataList.get (j);

                                pstmt.setInt( 1, inventoryData.getCsLineItemId() );
                                pstmt.setInt( 2, inventoryData.getLotId() );
                                pstmt.setInt( 3, inventoryData.getQuantityPull() );

                                pstmt.addBatch();
                            }

                            pstmt.executeBatch();
                            pstmt.clearBatch();
                        }
                    }   //if (finalInventoryDataList != null)

                    pstmt.close();
                    pstmt = null;*/

                //}   //if (finalInventoryMap != null)

            }   //if (lineItems != null)

            success = true;
            //system.out.println (success);
        }
        catch( Exception sqle )
        {
            //sqle.printStackTrace();
            throw new ScalarException ("Could not update customer shipping information into database", sqle);
        }
        finally
        {
            try {
                if (conn != null) {
                    if (success == true) {
                        //dbTransaction.commit();
                        conn.commit();//Bhaskar commented on 5 Nov
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }







    /**
    This method checks whether the quantity pulled is greater than the balance
    */

    /*private void checkForQtyPullGreaterThanBalance ( finalInventoryMap )
    {
        //HashMap error = new HashMap();
        if( ( finalInventoryMap != null ) && ( finalInventoryMap.size() != 0 ) )
        {
            Iterator finalInventoryDataListIterator = finalInventoryMap.values().iterator();
            ArrayList finalInventoryDataList = null;
            FinalInventoryData inventoryData = null;

            while( finalInventoryDataListIterator.hasNext() )
            {
                finalInventoryDataList = ( ArrayList ) finalInventoryDataListIterator.next();
                if (finalInventoryDataList != null)
                {
                    int size = finalInventoryDataList.size();
                    for( int i=0; i<size; i++ )
                    {
                        inventoryData = ( FinalInventoryData ) finalInventoryDataList.get( i );
                        //int lotId = inventoryData.getLotId();
                        String lotNumber = inventoryData.getLotNumber();
                        int balance =  inventoryData.getBalance();
                        //int csLineItemId = inventoryData.getCsLineItemId();
                        int quantityPull = inventoryData.getQuantityPull();
                        if ( quantityPull > balance )
                        {
                            //error.put ("The quantity  pulled for "+lotNumber+" is greater than balance", null);
                            System.out.println ("The quantity  pulled for "+lotNumber+" is greater than balance", null);
                        }
                    }
                }
            }
        }
    }*/



    /*
    This method is written to check the type of packing slip that is to be loaded. This is called from PackingSlipMenuAction.java
    Bhaskar on 4 Jan 2004
    */

    public static boolean selectPackingSlipType(int customerShippingId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean isDropShipment = true;

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.selectPackingSlipType"));

            pstmt.setInt (1, customerShippingId);

            rs = pstmt.executeQuery();

            if(rs.next())
            {
                if (rs.getInt("type") == 0)
                {
                    isDropShipment = false;
                }
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not fetch the Packing Slips from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return isDropShipment;
    }


    /*
    Bhaskar added this method on 13 Jan 2004 in order to get the fromAddressId that is used while
    saving/updating the drop shipment packing slip. In case the fromAddressId turns out to be -1
    then an exception is fired from the saveDropShipmentPackingSlip(***) method. This will counter
    the exception that may get fired incase there is on vendor with a finished goods location.
    */
    public static ArrayList getFromAddressId() throws ScalarException
    {
        ArrayList addressIds = new ArrayList();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String addressId = "";
        String vendor_detail = "";

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.dropshipment.getFromAddressId"));

            rs = pstmt.executeQuery();

            while(rs.next())
            {
                addressId = rs.getInt("location_id")+"";
                vendor_detail = rs.getString("l_vendor_name");
                addressIds.add(new LabelValueBean(vendor_detail, addressId));
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not fetch the Address Id from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        //system.out.println ("The size of the array List after the storage : "+ addressIds.size());
        return addressIds;
    }

    /*
    Bhaskar added this method on 16 Jan 2004 in order to get the fromLocationId that is just shown on the
    front end while displaying the issued packing slip. In case the fromAddressId turns out to be -1
    then no location can be obtained and such a situation will not arise because packing slip will not even
    get generated.
    */
    public static int getLocationIdFromAddressId(int fromAddressId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int fromLocationId = -1;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.dropshipment.getFromLocationId"));
            pstmt.setInt (1, fromAddressId);

            rs = pstmt.executeQuery();

            while (rs.next())
            {
                fromLocationId = rs.getInt("location_id");
                //system.out.println ("Line 2996 : "+fromLocationId);
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not fetch the Location_Id from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return fromLocationId;
    }

    /*
    Bhaskar added this method on 29 Jan 2004 to get the Shipping Contact Person from the Sales Order.
    This method will accept one argument and
    */

    public static String getShippingOrBillingContactPerson ( DBTransaction dbTransaction, String salesOrderId ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String contactPerson = "";

        try
        {
            if(dbTransaction != null)
                conn = dbTransaction.getConnection();
            else
                conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.packingslip.getShippingContactPerson"));
            try
            {
                pstmt.setInt (1, Integer.parseInt(salesOrderId.trim()));
            }
            catch(NumberFormatException e)
            {
                return contactPerson;
                /*The reason for returning "" as contact Person name in this block of code is like this.
                In case of non availability of the contact Person's name still the system should working
                normally without throwing major exceptions. Bhaskar on 29 Jan 2004*/
            }
            rset = pstmt.executeQuery();

            while (rset.next())
            {
                contactPerson = rset.getString ("contact_person");
            }
        }
        catch (Exception e)
        {
            throw new ScalarException ("Could not load Shipping Contact Person Details for Sales Order Id - " + salesOrderId, null);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return contactPerson;
    }

    // This process has to be done because for boards and kits
    // the part numbers are different but the inventory is pulled from the same
    // Parent product

    public static ArrayList getRepetedPartNumbers(ArrayList orderLineItemList) throws ScalarException
    {
        ArrayList repetedPartNumber = new ArrayList();
        ArrayList lineItemList = new ArrayList();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String lineItemString = "";
        String productType = "";

        if( orderLineItemList.size() > 1)
        {
            try
            {
                for( int i=0 ; i < orderLineItemList.size() ; i++ )
                {
                    LineItem lineItem = (LineItem) orderLineItemList.get(i);
                    //lineItemString = lineItemString + lineItem.getProductId() + "," ;
                    lineItemString = lineItemString + lineItem.getLineItemId() + "," ;
                }

                if( lineItemString.length() > 1 )
                {
                    lineItemString = lineItemString.substring( 0 , lineItemString.length() - 1 );
                }
                //System.out.println(" Line item String  "+ lineItemString);
                conn = DataSource.getConnection();
                pstmt = conn.prepareStatement ("select LineItem_ID, cool.Product_ID, P_Parent_Product_ID, ISNULL( P_type, 'CHIP') P_type from product ,Customer_Open_Order_LineItem cool where cool.Product_ID = Product.Product_ID and LineItem_ID in ( "+ lineItemString + ")");

                rset = pstmt.executeQuery();

                while (rset.next())
                {
                    productType = rset.getString( "P_type" );
                    LineItem li = new LineItem();

                    if( productType.equalsIgnoreCase("CHIP") )
                    {
                        li.setProductId( rset.getInt( "Product_ID" ) );
                        li.setLineItemId( rset.getInt( "LineItem_ID" ) );
                    }
                    else
                    {
                        if( rset.getInt( "P_Parent_Product_ID" ) == -1 )
                            li.setProductId( rset.getInt( "Product_ID" ) );
                        else
                            li.setProductId( rset.getInt( "P_Parent_Product_ID" ) );

                        li.setLineItemId( rset.getInt( "LineItem_ID" ) );
                    }
                    lineItemList.add( li );
                }

                for( int i=0 ; i < lineItemList.size()-1 ; i++ )
                {
                    LineItem lineItem = (LineItem) lineItemList.get(i);
                    int productID = lineItem.getProductId();

                    for(int j=i+1 ; j < lineItemList.size(); j++)
                    {
                        LineItem secLineItem = (LineItem) lineItemList.get(j);

                        int secProductID = secLineItem.getProductId();

                        if( productID == secProductID )
                        {
                            if( !repetedPartNumber.contains( lineItem.getLineItemId()+"" ) )
                                repetedPartNumber.add( lineItem.getLineItemId()+"" );
                            if( !repetedPartNumber.contains( secLineItem.getLineItemId()+"" ) )
                                repetedPartNumber.add( secLineItem.getLineItemId()+"" );

                                //System.out.println("    "+ lineItem.getLineItemId()+ "   "+secLineItem.getLineItemId() );
                        }
                    }
                }
            }
            catch (Exception e)
            {
                e.printStackTrace();
                throw new ScalarException ("Could not check for the repeted Part Numbers" , null);
            }
            finally {
                if (rset != null) {
                    try { rset.close(); } catch (SQLException ignore) {}
                }
                if (pstmt != null) {
                    try { pstmt.close(); } catch (SQLException ignore) {}
                }
                if (conn != null) {
                    try { conn.close(); } catch (SQLException ignore) {}
                }
            }
        }
        return repetedPartNumber;
    }

    // BR, Mar 13, 2004
    public static ShippingAdviseForm loadShippingAdviceById( int customerShippingId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        ShippingAdviseForm shippingAdviseForm = null;

        try
        {
            conn = DataSource.getConnection();
            // commented by Krishna Prasad on 08 June 2004
            //pstmt = conn.prepareStatement( "SELECT CST.LABEL[NUMBER], CST.CUSTOMERSHIPPING_ID, SO.SALESORDER_ID, SO.SALESORDER_NO, CUST.C_NAME, CST_VENDOR_PO_NUMBER, CST.INV_NO, CST.CST_FOB, CST.CST_BOX_COUNT, CST.CST_WEIGHT, CST.CST_AW_BILL_NUMBER, CST.CST_METHOD, CST.CST_FLIGHT_NUMBER, CST.CST_FLIGHT_ROUTE, CST.CST_ETD, CST.CST_ETA, CST.CST_HAWB, CST.CST_MAWB FROM CUSTOMER_SHIPPING_TABLE CST, SALES_ORDER SO, CUSTOMER CUST WHERE CST.CUSTOMERSHIPPING_ID = ? AND CST.SALESORDER_ID = SO.SALESORDER_ID AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID AND (CST_STATUS = 'ISSUED' OR CST_STATUS = 'SHIPPED')" );
            // Added by Krishna Prasad on 08 June 2004
            pstmt = conn.prepareStatement( "SELECT CST.LABEL[NUMBER], CST.CUSTOMERSHIPPING_ID, SO.SALESORDER_ID, SO.SALESORDER_NO, CUST.C_NAME, CST_VENDOR_PO_NUMBER, CST.INV_NO, CST.CST_FOB, CST.CST_BOX_COUNT, CST.CST_WEIGHT, CST.CST_AW_BILL_NUMBER, CST.CST_METHOD, CST.CST_FLIGHT_NUMBER, CST.CST_FLIGHT_ROUTE, CST.CST_ETD, CST.CST_ETA, CST.CST_HAWB, CST.CST_MAWB, CST.CST_STATUS, CST.CST_PALETTE_COUNT FROM CUSTOMER_SHIPPING_TABLE CST, SALES_ORDER SO, CUSTOMER CUST WHERE CST.CUSTOMERSHIPPING_ID = ? AND CST.SALESORDER_ID = SO.SALESORDER_ID AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID" );
            pstmt.setInt( 1, customerShippingId );
            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                shippingAdviseForm = new ShippingAdviseForm( rset.getString( "NUMBER" ),
                                                             rset.getInt( "CUSTOMERSHIPPING_ID" ),
                                                             rset.getInt( "SALESORDER_ID" ),
                                                             rset.getString( "SALESORDER_NO" ),
                                                             rset.getString( "C_NAME" ),
                                                             rset.getString( "CST_VENDOR_PO_NUMBER" ),
                                                             rset.getString( "INV_NO" ),
                                                             rset.getString( "CST_FOB" ),
                                                             rset.getString( "CST_BOX_COUNT" ),
                                                             StringUtils.formatAmount( rset.getFloat( "CST_WEIGHT" ) ),
                                                             StringUtils.toStringArray( rset.getString( "CST_AW_BILL_NUMBER" ), ";" ),
                                                             rset.getString( "CST_METHOD" ),
                                                             rset.getString( "CST_FLIGHT_NUMBER" ),
                                                             rset.getString( "CST_FLIGHT_ROUTE" ),
                                                             rset.getString( "CST_ETD" ),
                                                             rset.getString( "CST_ETA" ),
                                                             rset.getString( "CST_HAWB" ),
                                                             rset.getString( "CST_MAWB") );

                shippingAdviseForm.setStatus ( rset.getString( "CST_STATUS" ) ); // Added by Krishna 08 June 2004
                shippingAdviseForm.setPaletteCount( rset.getString( "CST_PALETTE_COUNT" ) );//Added by varma 15 oct 2004
            }

            pstmt.close();
            rset.close();

            pstmt = conn.prepareStatement( "SELECT CSLNT.CSLIT_ID, LT.LOT_ID, LT.LOT_NUMBER, CSLNT_QTY_PULL, COOL.COOL_LINE_ITEM_NO, CSLNT.TRACKING_NUMBER FROM CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLNT, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, CUSTOMER_SHIPPING_TABLE CST, LOT_TABLE LT, CUSTOMER_OPEN_ORDER_LINEITEM COOL WHERE CST.CUSTOMERSHIPPING_ID = ? AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID AND CSLIT.CSLIT_ID = CSLNT.CSLIT_ID AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND CSLNT.LOT_ID = LT.LOT_ID" );
            pstmt.setInt( 1, customerShippingId );
            rset = pstmt.executeQuery();

            String trackingNumber = null;
            HashMap pulledLotMap = new HashMap();
            ArrayList lotWithTrackingNumberList = null;
            CustomerShippingLotNumberData pulledLot = null;

            while( rset.next() )
            {
                pulledLot = new CustomerShippingLotNumberData( rset.getInt( "CSLIT_ID" ),
                                                                      rset.getInt( "LOT_ID"),
                                                                      rset.getString( "LOT_NUMBER" ),
                                                                      rset.getInt( "CSLNT_QTY_PULL" ),
                                                                      rset.getString( "COOL_LINE_ITEM_NO" ),
                                                                      rset.getString( "TRACKING_NUMBER" ) );
                trackingNumber = pulledLot.getTrackingNumber();
                /*if( trackingNumber == null )
                {
                    trackingNumber = "NO_TRACKING_NUMBER";
                }*/

                lotWithTrackingNumberList = ( ArrayList ) pulledLotMap.get( trackingNumber );
                if( lotWithTrackingNumberList == null )
                {
                    lotWithTrackingNumberList = new ArrayList();
                }

                lotWithTrackingNumberList.add( pulledLot );
                pulledLotMap.put( trackingNumber, lotWithTrackingNumberList );
            }

            shippingAdviseForm.setPulledLotMap( pulledLotMap );
        }
        catch( Throwable t )
        {
            t.printStackTrace();
            throw new ScalarException( "Could not fetch the Shipping Advice details from database", t );
        }
        finally
        {
            if( rset != null )
            {
                try { rset.close(); } catch( SQLException excp ) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException excp ) {}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return shippingAdviseForm;
    }

    // BR, Mar 13, 2004
    public static void updateShippingAdvise( DBTransaction dbTransaction, ShippingAdviseForm shippingAdviseForm )
        throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        if( shippingAdviseForm == null ) return;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "UPDATE CUSTOMER_SHIPPING_TABLE SET CST_FLIGHT_NUMBER = ?, CST_FLIGHT_ROUTE = ?, CST_ETD = ?, CST_ETA = ?, CST_HAWB = ?, CST_MAWB = ?, CST_METHOD = ?, CST_FOB = ?, INV_NO = ?, CST_VENDOR_PO_NUMBER = ?, CST_BOX_COUNT = ?, CST_WEIGHT = ?, CST_AW_BILL_NUMBER = ?, CST_PALETTE_COUNT = ? WHERE CUSTOMERSHIPPING_ID = ?" );

            pstmt.setString( 1,  shippingAdviseForm.getFlightNumber() );
            pstmt.setString( 2,  shippingAdviseForm.getFlightRoute() );
            pstmt.setString( 3,  shippingAdviseForm.getEtd() );
            pstmt.setString( 4,  shippingAdviseForm.getEta() );
            pstmt.setString( 5,  shippingAdviseForm.getHawb() );
            pstmt.setString( 6,  shippingAdviseForm.getMawb() );
            pstmt.setString( 7,  shippingAdviseForm.getShippingMethod() );
            pstmt.setString( 8,  shippingAdviseForm.getDeliveryTerms() );
            pstmt.setString( 9,  shippingAdviseForm.getInvoiceNumber() );
            pstmt.setString( 10, shippingAdviseForm.getPoNumber() );
            pstmt.setString( 11, shippingAdviseForm.getCartonCount() );
            pstmt.setString( 12, shippingAdviseForm.getWeight() );
            pstmt.setString( 13, shippingAdviseForm.getTrackingAsString() );
            pstmt.setString( 14, shippingAdviseForm.getPaletteCount() );
            pstmt.setInt(    15, shippingAdviseForm.getCustomerShippingId() );


            pstmt.execute();
            pstmt.close();

            pstmt = conn.prepareStatement( "UPDATE CUSTOMER_SHIPPING_LOT_NUMBER_TABLE SET TRACKING_NUMBER = ? WHERE CSLIT_ID = ? AND LOT_ID = ?" );
            HashMap pulledLotMap = shippingAdviseForm.getPulledLotMap();
            CustomerShippingLotNumberData pulledLot = null;

            if( ( pulledLotMap != null ) && ( pulledLotMap.isEmpty() == false ) )
            {
                Iterator pulledLotListIterator = pulledLotMap.values().iterator();
                ArrayList lotList = null;

                while( pulledLotListIterator.hasNext() == true )
                {
                    lotList = ( ArrayList ) pulledLotListIterator.next();

                    if( ( lotList != null ) && ( lotList.isEmpty() == false ) )
                    {
                        int size = lotList.size();
                        for( int i=0; i<size; i++ )
                        {
                            pulledLot = ( CustomerShippingLotNumberData ) lotList.get(i);
                            pstmt.setString( 1, pulledLot.getTrackingNumber() );
                            pstmt.setInt( 2, pulledLot.getId() );
                            pstmt.setInt( 3, pulledLot.getLotId() );

                            pstmt.addBatch();
                        }

                        pstmt.executeBatch();
                        pstmt.clearParameters();
                    }
                }
            }

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not update Shipping Advise details", sqle);
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore ) {}
            }

            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }

            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
    }


    // BR, Mar 13, 2004
    public static String[] loadCustomerShipAlertEmails( String customerName ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String emails = null;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( "SELECT EMAILS FROM CUSTOMER_SHIP_ALERT_EMAILS_TABLE WHERE CUSTOMER_NAME = ?" );
            pstmt.setString( 1, customerName );

            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                emails = rset.getString( "EMAILS" );
            }
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not retrieve Customer Ship Alert Email list from database", e );
        }
        finally
        {
            if( rset != null )
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        String[] emailArray = StringUtils.toStringArray( emails, Mailer.EMAIL_SEPARATOR );

        if( ( emailArray != null ) && ( emailArray.length > 0 ) )
        {
            Arrays.sort( emailArray );
        }

        return emailArray;
    }


    // BR, Mar 13, 2004
    public static String[] loadClientEmailsForShipAlert() throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String emails = null;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( "select Ship_Alert_Emails from notification_emails_table" );
            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                emails = rset.getString( "Ship_Alert_Emails" );
            }
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not retrieve Ship Alert Email list from database", e );
        }
        finally
        {
            if( rset != null )
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        String[] emailArray = StringUtils.toStringArray( emails, Mailer.EMAIL_SEPARATOR );

        if( ( emailArray != null ) && ( emailArray.length > 0 ) )
        {
            Arrays.sort( emailArray );
        }

        return emailArray;
    }

    // BR, March 16, 2004
    public static boolean updateLotTrackingNumber( DBTransaction dbTransaction, String cslitId, String lotId, String trackingNumber )
        throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        boolean rowsUpdated = false;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "UPDATE CUSTOMER_SHIPPING_LOT_NUMBER_TABLE SET TRACKING_NUMBER = ? WHERE CSLIT_ID = ? AND LOT_ID = ?" );

            pstmt.setString( 1, trackingNumber );
            pstmt.setString( 2, cslitId );
            pstmt.setString( 3, lotId );

            rowsUpdated = ( pstmt.executeUpdate() > 0 );

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not update Shipping Advise details", sqle);
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore ) {}
            }

            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }

            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return rowsUpdated;
    }

    // Added by Krishna 08 June 2004
    public static int getCstShippingIdByRandomKey(int cstRandomKey) throws ScalarException
    {

        int cstShipId = -1 ;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select CustomerShipping_ID from Customer_Shipping_Table where CST_Random_Key = ?" );
            pstmt.setInt ( 1 , cstRandomKey );
            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                cstShipId = rset.getInt( "CustomerShipping_ID" );
            }
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not retrieve Shipping Id with Random Number from database", e );
        }
        finally
        {
            if( rset != null )
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return cstShipId;
    }

    /**
     *
     * @return
     * @throws ScalarException
     * @param dbTransaction
     */
    public static String getNextPsInvoiceNumber( DBTransaction dbTransaction ) throws ScalarException
    {

        Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rs                = null;
        String psInvoiceNo          = "";
        String nextPsInvoiceNo      = "";
        int invoiceNo               = 1;
        Calendar calendar           = Calendar.getInstance();

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            //pstmt = conn.prepareStatement("select INV_NO from CUSTOMER_SHIPPING_TABLE where CustomerShipping_ID = ( select max( CustomerShipping_ID ) from CUSTOMER_SHIPPING_TABLE WHERE INV_NO IS NOT NULL AND INV_NO != '' )");
            pstmt = conn.prepareStatement("SELECT INV_NO FROM CUSTOMER_SHIPPING_TABLE WHERE CST_CONFIRMED_ON = ( SELECT MAX( CST_CONFIRMED_ON ) FROM CUSTOMER_SHIPPING_TABLE WHERE INV_NO IS NOT NULL AND INV_NO != '' )");
            rs = pstmt.executeQuery();

            if (rs.next())
            {
                psInvoiceNo = rs.getString("INV_NO");

                try
                {
                    invoiceNo = Integer.parseInt(psInvoiceNo.substring(4));
                    invoiceNo = invoiceNo + 1;

                    if (invoiceNo > 9999)
                    {
                        psInvoiceNo = "0001";
                    }
                    else if(invoiceNo > 999)
                    {
                       psInvoiceNo = invoiceNo+"";
                    }
                    else if (invoiceNo > 99 && invoiceNo <= 999 )
                    {
                        psInvoiceNo = "0" + invoiceNo;
                    }
                    else if (invoiceNo >= 10 && invoiceNo <= 100 )
                    {
                        psInvoiceNo = "00" + invoiceNo;
                    }
                    else if (invoiceNo < 10)
                    {
                        psInvoiceNo = "000" + invoiceNo;
                    }
                }
                catch (Exception e)
                {
                    psInvoiceNo = "0001";
                }
            }
            else
            {
                psInvoiceNo = "0001";
            }

            java.util.Date now = calendar.getTime();
            String yearLastTwoDigits = StringUtils.formatDateToString(now, "yy");
            String monthDigits = StringUtils.formatDateToString(now, "MM");

            nextPsInvoiceNo = yearLastTwoDigits + monthDigits + psInvoiceNo;
        }
        catch (Exception e)
        {
            throw new ScalarException("Could not fetch the Packing Slip Invoice Number from the database.");
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return nextPsInvoiceNo;
    }
}